Customising sort order in MySQL

I came across a situation the other day where I needed to sort my result set in the database for efficiency: we were selecting a small number of a very large result set, so sorting on the client would require the entire data set to travel over the wire. Unfortunately, the requirements of the front end weren’t compatible with MySQL sort order, since the customer wanted empty strings sorted to the bottom of the list.

In other words, I wanted to be able to do something like the following:

  SELECT id, name
    FROM people
   LIMIT 20;

and end up with the result set

id name
3 Alice
7 Bob

Changing the schema wasn’t an option, and nor was doing the majority of the sort in MySQL and post-filtering, since that would cause the wrong number of results to be returned after some had been shuffled to the bottom of the list. Luckily, there’s a quick hack in MySQL that allows this sort of thing to be done:

  SELECT id, name
    FROM people
ORDER BY FIELD(name, ''), name
   LIMIT 20;

This works because the FIELD() function returns the index of the name value in the list of fields given, and 0 if it is not present. That is, if the name is an empty string, this expression will return 1, and if not it will return 0. This causes empty strings to be sorted to the end of the list as desired.

Note that this prevents an index from being used for the ordering, which may be a problem depending on the size of the result set. In my case, the query was such that an index couldn’t be used anyway, so there was no substantial loss of efficiency.

Leave a Reply

Your email address will not be published. Required fields are marked *