5

I'm using mySQL. I have to order names of Contacts by Lastname but in the case that there is no last name, I order by firstname.

This looks like:

ORDER BY lastname = "", lastname, firstname

However, this makes the ones with lastnames appear at the top. The behaviour I'd like is to intermix the first and lastnames like they were from the same field.

Example (pretend these are names):

A,T 
Z,G 
A 
B 
C

Versus:

A
A,T
B
C
Z,G

Thanks

John Hartsock
  • 85,422
  • 23
  • 131
  • 146
Tyler
  • 55
  • 1
  • 5

4 Answers4

21

Use COALESCE and NULLIF:

ORDER BY COALESCE(NULLIF(LastName, ''), FirstName), FirstName
degenerate
  • 1,224
  • 1
  • 14
  • 35
Cade Roux
  • 88,164
  • 40
  • 182
  • 265
  • This is the one!! Great adaption of the first solution so it works on empty fields. – Tyler Dec 22 '10 at 19:33
  • 1
    +1 great solution...didnt even occur to me...but I still dont agree with the empty strings values. – John Hartsock Dec 22 '10 at 19:37
  • @John Hartsock - It depends on the database design and conventions in the shop. And I agree, in general, I would expect '' LastName for someone like Pele (known to be blank) and NULL LastName for Bob from Office Space (unknown, but assumed to perhaps exist). In this case, I imagine there is no difference in the desired sorting behavior, however. – Cade Roux Dec 22 '10 at 19:57
9

Try using Coalesce
Note: this would require you not to store empty last names using an empty string (ie "")

ORDER BY Coalesce(LastName, FirstName)

As Suggested in the Comments By adding FirstName to the order By list again you will properly order two people with the same lastName. Here is an example.

ORDER BY Coalesce(LastName, FirstName), FirstName
John Hartsock
  • 85,422
  • 23
  • 131
  • 146
  • ...and add Firstname once more after that, so that two contacts with the same Lastname will be correctly ordered. – Axel Dec 22 '10 at 19:14
  • I just tried that and the Contacts now appear to be in almost random order. It was definitely more organized before the Coalesce... – Tyler Dec 22 '10 at 19:16
  • I tried it with Axel's suggestion, and it looks the same as ORDER BY lastname, firstname. I'm joining several tables in the query, if that matters. – Tyler Dec 22 '10 at 19:18
  • @Tyler I just ran it on a sample Table and it worked as expected? So I'm very confused from your previous statement. – John Hartsock Dec 22 '10 at 19:19
  • +1 for elegant suggestion. However, if the values of the columns are not `NULL`, `COALESCE()` may not give the precise order for the OP. – Jason McCreary Dec 22 '10 at 19:20
  • @Tyler make sure you are aliasing your fields from your joined tables. Or perhaps it would be easier to show us the entire query? – John Hartsock Dec 22 '10 at 19:20
  • @Jason McCreary Storing an empty string for a value never entered is a bad idea IMO. If there is nothing to store then Null is the correct value. – John Hartsock Dec 22 '10 at 19:22
  • I do store lastnames as "" when none exists. SELECT firstname, lastname, contacts.id, contactPerson.id AS personID FROM contacts JOIN contactPerson ON contacts.fid = contactPerson.id ORDER BY – Tyler Dec 22 '10 at 19:23
  • @John, completely agree. But based on `lastname = ""` in the OP and the fact that `COALESCE` didn't seem to work, I thought this may be the issue. – Jason McCreary Dec 22 '10 at 19:24
  • @Tyler why are you storing last name as empty string? Is it true that thier last name is "" or is it true that thier last name is unknown. I would bet its the latter. Therefore you are representing your data inaccurately. Null is the right choice when the last name value is unknown. – John Hartsock Dec 22 '10 at 19:26
  • Thanks for the tip about "". I already have a ton of data in the system, is there an easy way to transition to null? I used "" because that's the value the app want's to see when the lastname doesn't exist. – Tyler Dec 22 '10 at 19:28
  • 1
    @Tyler there is an easy way for that one column. `Update yourtable set lastname = null where lastname = ''` Note whatever is inputing the data into the database will need to be updated as well. – John Hartsock Dec 22 '10 at 19:30
  • In some systems, for instance Oracle, there is no distinction between empty varchar columns and NULL. http://stackoverflow.com/questions/203493/why-does-oracle-9i-treat-an-empty-string-as-null – Cade Roux Dec 22 '10 at 19:33
  • +1 for hivemind on comments we posted to our own answers stressing the difference between "" and null – rownage Dec 22 '10 at 19:33
  • @Cade Roux ... that is an excellent point. however it is irrelevant to the question and problem at hand since OP is using MYSQL. – John Hartsock Dec 22 '10 at 19:34
  • When using NULL, I'll have to recheck all the places I add data and remove data, so that adds another layer of abstraction (per se) such that I'm converting the actual value into something more meaningful to mySQL. Aside from being a general rule, would using NULL really make my system better or just more complicated? – Tyler Dec 22 '10 at 19:34
  • @Tyler No im not saying refactor everything. I just provided you some suggestions. Surely you dont want to do that immediately as an adhoc change but Consider it for a future revision and future handling of data. As I stated `Is it true that their last name is "" or is it true that thier last name is unknown. I would bet its the latter. Therefore you are representing your data inaccurately. Null is the right choice when the last name value is unknown.` Clearly a change like this to your app would require planning. Plan it first inorder to consider the ramifications, cost, time, etc. – John Hartsock Dec 22 '10 at 19:41
  • As far as my app is concerned the lastname is "". I guess that's the best argument I have, and the fact that I'd have to convert "" -> null on the way in, and null -> "" on the way out which seems counter-intuitive since the app does not deal with null. Maybe the Persons lastname isn't "" in reality, but in the context of the app that uses the database, the lastname definitely is "". Are there really no exceptions to this rule? – Tyler Dec 22 '10 at 19:50
  • @Tyler...Sure there can be exceptions and that is your design not mine. I was just stating my opinion. – John Hartsock Dec 22 '10 at 19:52
  • http://stackoverflow.com/questions/471367/when-to-use-null-in-mysql-tables. Per some of the suggestions there, my take on this is "use NULL if there is a missing or not applicable value that can go there", but in my case "" is very applicable in regards to the app. For example, "we looked for the last name and couldn't find it for some reason" i'd want that to be NULL, but "we looked at the lastname and it was blank" is a specific value that happens to be "", not an indeterminate. I'm not trying to start a fight, just trying to understand the rules. – Tyler Dec 22 '10 at 19:54
  • Which versions of MYSQL supports COALESCE in order by clause? – Gaurav Patil Mar 03 '23 at 19:22
3

ORDER BY supports custom sorting. But given your logic, I would suggest creating a field in your SELECT with CONCAT and then ordering on it.

SELECT *, IF(LENGTH(lastname) = 0, firstname, CONCAT(lastname, ', ', firstname)) AS fullname
FROM contacts
ORDER BY fullname;

This also has the benefit of returning fullname in your results based on your same sorting logic.

Jason McCreary
  • 71,546
  • 23
  • 135
  • 174
  • +1 although I dont agree with using empty string in the last name column. I do agree that your solution will work for OP's question – John Hartsock Dec 22 '10 at 19:32
0
ORDER BY
CASE  
  WHEN LName is null
  THEN FName 
  ELSE LName
  END

more here

Community
  • 1
  • 1
rownage
  • 2,392
  • 3
  • 22
  • 31