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.


We'd love to hear from you!

If you think Bronco has the skills to take your business forward then what are you waiting for?

Get in Touch Today!

Discussion

Write a comment...
  • Zeeshan Ahmad

    What a wonderful trick and solution for many e-commerce platforms.

Add a Comment

Get in touch