How to put the rows of a query to the bottom if a column has a certain value ordered by another value?

Question

I have a table products with 3 columns:
- id_product
- quantity_in_stock
- product_name

I want to have all the rows ORDERED BY product_name with quantity_in_stock at the bottom of my result if it's = 0.

I tried this query but it doesn't 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)

Maybe there is a simpler way to do that but it's monday! ;)

Answer 1

The ORDER BY can contain an arbitrary expression, so you can evaluate those which = 0 and assign them a higher value which sorts 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

Because of MySQL's boolean evaluation returning 1 or 0, you can simplify this as below. It won't work in every RDBMS though:

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 though, if quantity_in_stock is not indexed, query performance could be affected negatively by this.