MySql sort conditional based on column values 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 &gt;= 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 &gt;= 100 DESC, price ASC, stock DESC


How can I make this work?
I think this would work - although it's rather obfuscated:

ORDER BY 
    price = 0, 
    LEAST(stock, 100) DESC, 
    price ;


More clearly:

ORDER BY 
    CASE WHEN price &gt; 0 THEN 1 ELSE 2 END, 
    CASE WHEN stock &gt;= 100 THEN 100 ELSE stock END DESC, 
    price ;