Search code examples
mysql

Return 50% of random result

I want to get 50% randomly form the result set. What I've tried which is close:

SELECT * FROM Customers
WHERE RAND() < 0.5;

But this gives me less or more than 50% of the result set. In other words, this is not stable.

The second try is as below:

SELECT * FROM Customers
ORDER BY RAND()
LIMIT (SELECT COUNT(*) / 2 FROM Customers);

But I have an error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(SELECT COUNT(*) / 2 FROM Customers)' at line 3.

As you can test here: https://www.w3schools.com/MySQL/trymysql.asp?filename=trysql_select_all

The expected result should be making the order to be random, and then select 50% of the result set.

Solution

  • On MySQL 8+, we can use ROW_NUMBER() here:

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

SELECT *
FROM cte
WHERE rn <= cnt / 2;