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

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?

CodePudding user response：

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

CodePudding user response：

Use a case statement to create a field to order by

e.g.

CREATE TABLE IF NOT EXISTS `products` (
  `id` int(6) unsigned NOT NULL,
  `stock` int(3) unsigned NOT NULL,
  `product` varchar(200) NOT NULL,
  PRIMARY KEY (`id`,`product`)
) DEFAULT CHARSET=utf8;
INSERT INTO `products` (`id`, `stock`, `product`) VALUES
  ('1', '10', 'Timber'),
  ('2', '12', 'Nails'),
  ('1', '0', 'Glue'),
  ('1', '0', 'Left handed wrench.');

And run

SELECT stock, product, case when stock > 0 then 1 else 2 end as SetOrder
FROM products 
ORDER BY SetOrder, RAND()

Gets you

stock product SetOrder
10 Timber 1
12 Nails 1
0 Glue 2
0 Left handed wrench. 2

SQL Fiddle