How to Order in MySQL, putting Zero or NULL at the end

You may find yourself in a situation where you want to order a certain column in ascending order but have zero or NULL values at the end of the list rather than the start.

Zero

This happened to me where a client had certain products with no prices but didn’t want them showing up at the top of the list when a user ordered the products by price, low to high.

The solution looks strange but is very straightforward; say your field is called “price”, you simply include price = 0 in your ORDER clause, as follows:

ORDER BY price = 0, price ASC

The result is a list of products in ascending order based on their price as you’d expect, but with anything with a price of 0 at the end of the list.

NULL

If your database field contains NULL values here’s another trick:

ORDER BY -price DESC

This is basically price ASC, so you get an ascending list of product prices as above, while putting NULL values at the end.

This code is free to use at your own discretion. It comes without warranty. Please feel free to feedback any edits.