mariadb

Mariadb order by any positive number first

I want to sort products by newness first, i.e. date on which they were added but in stock products should come before out of stock products. I have a column of "stock" that tracks how many units I have in stock.

SELECT * FROM products ORDER BY stock DESC, date_added DESC

This simple query doesn't work because it will sort by stock first and if 2 products have same stock number, they'll be sorted by date. I need a way to convert stock into two numbers 1 (has stock) and 0 (no stock) and then sort them by date.

Can this be done?

Solution

  • You can do something like this:

    SELECT * 
FROM products 
ORDER BY 
    CASE WHEN stock > 0 THEN 1 ELSE 0 END DESC, 
    date_added DESC;