SQL sorting when sort field is null

May 2, 2008 7:09 PM

MySQL sorting issue. When I have "first name" and "last name" columns, and I'm sorting primarily by "last name." Some entries have only a "first name," and rather than have those end up at either the start or end of the sort, I'd like it to pretend the "first name" IS the "last name" and sort accordingly. Is there a way to do this with the SQL SELECT statement or should I just edit the data?

So, I've got this list of names like:

Ron
Bill Smith
Adam Jones
Steve Washington

They're split up into "firstName" "lastName" fields. For purposes of the webpage they're getting displayed on, I'm concatenating them into a single "fullName" field, and will be sorting "ORDER BY lastName." Except a few names only have "firstName." Left alone, MySQL seems to put those at the beginning of the list, and I've seen some info on ways to push them to the bottom - but what I want, is when "lastName is null" to have it use "firstName" instead, so that the list above would end up:

Adam Jones
Ron
Bill Smith
Steve Washington

I realize one option would be to just edit the data and wherever there's a first name only to move that into the "last name" field (because once concatenated the displayed name will be the same) but that just seems like a bad solution in the long term, if there's a way to make the SQL do this for me.

(I'm pretty new to MySQL so forgive me if there's some obvious answer. I've searched forums via Google but I can't find questions quite like this.)

posted by dnash to computers & internet (8 comments total)

SELECT IFNULL(last_name, first_name) as my_name FROM my_table
ORDER BY my_name ASC;
posted by SPrintF at 7:31 PM on May 2

SPrintF is correct, but there's a possible gotcha, IFNULL only works if the field is NULL, not if the field is 'blank'. It's been my experience that many times a field contains the blank string rather than NULL. So, depending on your database, you might need to do something like
SELECT IF(last_name!='',last_name, first_name) as my_name FROM my_table
ORDER BY my_name ASC;
posted by fishfucker at 8:31 PM on May 2

1. Add a "sortable name" column.

2. Add an index for it.

3. Write a stored procedure to populate it, based on the contents of the first and last name fields.

4. Change the "on insert" and "on update" triggers of the table to run that stored procedure.

5. Run that stored procedure on the existing data.

This will be more work in the short term, but your sorting will be more efficient (at the cost of less efficient inserts/updates).
posted by Flunkie at 8:36 PM on May 2

I think you want the "coalesce" function.

SELECT first_name, last_name
FROM people
ORDER BY COALESCE(last_name, firstname), firstname
posted by cmiller at 9:01 PM on May 2

You could add a calculated field that's the concatenation of lastname + firstname, and sort on that but not have it displayed.
posted by ctmf at 9:31 PM on May 2

alternatively, you could select a concatcenation of the lastname and firstname, and sort by that:

SELECT firstname, lastname, CONCAT(lastname, firstname) AS sortname ORDER BY sortname

(the above answers are probably more efficient, if your version of MySQL supports such things.)
posted by neckro23 at 9:32 PM on May 2

That is, have the client side form calculate the concatenation, then save firstname, lastname, and fullname in the table. Your select query could order by fullname, but the user-visible form need not actually display that value.

I'm sure that would be inefficient for big high-use databases (I'm no expert).
posted by ctmf at 9:38 PM on May 2

Thanks, folks. I was able to make SPrintF's solution work just fine!

I ended up with:

SELECT CONCAT_WS(' ', firstName, middleName, lastName) AS stageName,
IFNULL(lastName, firstName) as sortName,

(etc... other sort fields and "WHERE" arguments)

ORDER BY sortName


Fishfucker, thanks also . In this case I definitely know that the fields in question are NULL, cuz they're showing up that way in the MySQL Query Browser, but I'll keep that in mind for the future.
posted by dnash at 10:11 PM on May 2