Mariadb order by any positive number first - Stack Overflow

admin管理员组

文章数量:1406327

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?

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?

• mariadb

Share Improve this question Follow asked Mar 6 at 5:28 WhipWhip 2,2542626 silver badges5050 bronze badges 2

• Please share sample data and expected result – Jens Commented Mar 6 at 5:37
• 1 I need a way to convert stock into two numbers 1 (has stock) and 0 (no stock) and then sort them by date. ORDER BY stock='has stock' DESC, date_added DESC – Akina Commented Mar 6 at 5:51

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

You can do something like this:

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

• CASE WHEN stock > 0 THEN 1 ELSE 0 END DESC will make sure that all products that are in stock are ranked first (1 > 0)
• date_added DESC will make sure that the newer ones will appear first

本文标签： Mariadb order by any positive number firstStack Overflow