I have a table with products where there's a column for price and stock.
I want the products to be display sorted by the following rules:
- All products with price = 0 go to the bottom;
- Then the products with price are sorted by:
- if stock is greater or equal to 100 then they are sorted by price ASC;
- else they are sorted by stock DESC and if there's a draw then sorted by price ASC.
So far I came up with
price = 0, stock >= 100 DESC, price ASC, stock DESC
which seems to work correctly when there's products with stock over 100 but falls apart when there are none.
SELECT * FROM products ORDER BY price = 0, stock >= 100 DESC, price ASC, stock DESC
How can I make this work?