1

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?

  • @ypercubeᵀᴹ, what I have is something like this: SELECT * FROM products ORDER BY price = 0, stock >= 100 DESC, price ASC, stock DESC (I don't have access to the whole query because I'm using a framework but have access to the order part) – The Berga Sep 4 '18 at 15:33
2

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 > 0 THEN 1 ELSE 2 END, 
    CASE WHEN stock >= 100 THEN 100 ELSE stock END DESC, 
    price ;
| improve this answer | |
  • Used the first one and worked perfectly. Thanks. – The Berga Sep 4 '18 at 16:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.