How to put query strings at the bottom if a column has a specific value, ordered by another value?

Question

I have a product table with 3 columns:
- id_product
- number_in_stock
- product_name

I want to have all the lines ORDERED BY product_name with number_values ​​at the bottom of my result if it = 0.

I tried this request but it does not work:

(SELECT * FROM products WHERE quantity_in_stock != 0 ORDER BY product_name ASC) UNION (SELECT * FROM products WHERE quantity_in_stock = 0 ORDER BY product_name ASC) 

There may be an easier way to do this, but it’s peace !;)

Answer 1

ORDER BY can contain an arbitrary expression, so you can evaluate those = 0 and assign them a higher value, which is sorted later:

 SELECT * FROM products ORDER BY /* If quantity_in_stock is 0, assign a 1 otherwise 0. The ones sort after 0 */ CASE WHEN quantity_in_stock = 0 THEN 1 ELSE 0 END ASC, /* Then sub-order by name */ product_name ASC 

Due to the boolean evaluation of MySQL returning 1 or 0, you can simplify this as shown below. However, this will not work in all DBMSs:

 SELECT * FROM products ORDER BY /* Will return 0 (false) for quantity_in_stock <> 0 and 1 if true */ (quantity_in_stock = 0) ASC, product_name ASC 

Beware, however, if quantity_in_stock not indexed, query performance may adversely affect this.