MySQL get 2% of the record

user_16622349 0
Viewed 43

I am trying to get 2% of the random sample record.

SELECT * FROM Orders
ORDER BY RAND()
LIMIT (SELECT CEIL(0.02 * (SELECT COUNT(*) FROM Orders)));

This one gives a syntax error due to line 3. Is there anything I am doing wrong? Or is there a better way to get n % of records?

limit mysql
1 Answers

If you are using MySQL 8+, then ROW_NUMBER() provides one option:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY RAND()) rn,
              COUNT(*) OVER () cnt
    FROM Orders
)

SELECT *
FROM cte
WHERE 1.0*rn / cnt <= 0.02;

On MySQL 5.7 and earlier, we can simulate row number:

SELECT *
FROM
(
    SELECT *, (@rn := @rn + 1) AS rn
    FROM Orders, (SELECT @rn := 0) AS x
    ORDER BY RAND()
) t
CROSS JOIN (SELECT COUNT(*) AS cnt FROM Orders) o
WHERE 1.0*rn / cnt <= 0.02;
user_18632290
Related