After explaining How to Order in MySQL, putting Zero or NULL at the end today we’ll be looking at how to order based on two columns, one of which may have either NULL or empty values in.
I came across this problem on a client’s site where we have an “alternative name” field for each product. This is so the client can, where necessary, display a different name on the front end of the site from what they see in admin. The problem here is that not all products have an alternative name so if we want to order by the product name in MySQL, we have to take both the “name” and “alternative name” fields into account.
We acheive this like so:
SELECT COALESCE(NULLIF(alternative_name, ''), name) AS name ORDER BY name
This replaces any empty values with NULL then the COALESCE function picks the first non-NULL value.
If your database has NULLs instead of empty values you can leave the first step out and simply have:
SELECT COALESCE(alternative_name, name) AS name ORDER BY name
This code is free to use at your own discretion. It comes without warranty. Please feel free to feedback any edits.
Like what you’ve read, then why not tell others about it... they might enjoy it too
If you think Bronco has the skills to take your business forward then what are you waiting for?Get in Touch Today!