Search code examples
mysqlsql-order-byunion

Why is the `ORDER BY RAND()` statement not working in my query?

I have a database where I save information about my products. I use a query for getting those products from my table. The query looks like this:

SELECT * FROM products WHERE stock > 0 ORDER BY RAND();

This query returns all the products that have stock > 0 in a random order, and it works ok. However, now I want to get those products with stock = 0, but I want them to appear at the end of the query (also in a random way but always after products that have stock > 0). So I tried a new query which looks like this:

(SELECT * FROM products WHERE stock > 0 ORDER BY RAND()) 
UNION
(SELECT * FROM products WHERE stock = 0 ORDER BY RAND());

...this query returns the zero-stock products at the end, but it seems to ignore the ORDER BY RAND() statement and I always get them in the same order. So my question is: how can I get a random response from the query mantaining the condition of zero-stock products at the end?

Solution

  • You don't need UNION:

    SELECT * 
FROM products 
ORDER BY stock = 0, RAND();

    The condition stock = 0 in the ORDER BY clause makes sure that the zero-stock products are placed last and the 2nd level of sorting with RAND() randomizes the rows in each of the 2 groups.

    SQL Fiddle