I thought of a great way to order results in MySQL


While writing a Power Search module for my accounting/customer management software, I came up with a great way to order results in MySQL.

The problem was this: some of our accounts are listed under a business name, and some are listed under a person's name.  You couldn't order by `BusinessName`,`LastName` because all the accounts that didn't have a business name listed would appear first in the list.  If you reversed it, all the accounts who had only a business name would appear first in the list.  The solution, fortunately, was beyond simple:

order by CONCAT(`BusinessName`,`LastName`,`FirstName`)

This is equivalent to saying "Sort by BusinessName, if it doesn't exist then sort by LastName, if it doesn't exist then sort by FirstName.  Yay!
 

If you would like a reply to your comment, you must leave your email address! We receive dozens of questions every month from people who don't leave us with any way to contact them, so we have no choice but to ignore the question. We try to reply to as many questions as we can, if we know the email address of the person who asked the question. Thanks in advance for writing in :)

Allowed HTML: <b>, <i>, <em>, <strong>. All other < and > will be replaced with &lt; and &gt;.