CODEWITHSUNDEEP
sqlmysql
Tyler
Tyler

Reputation: 55

SQL: ORDER BY two columns intermixed, not priority based

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

Upvotes: 5

Views: 7665

Answers (4)

Cade Roux
Cade Roux

Reputation: 89661

Use COALESCE and NULLIF:

ORDER BY COALESCE(NULLIF(LastName, ''), FirstName), FirstName

Upvotes: 21

John Hartsock
John Hartsock

Reputation: 86872

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

Upvotes: 9

rownage
rownage

Reputation: 2404

ORDER BY
CASE  
  WHEN LName is null
  THEN FName 
  ELSE LName
  END

more here

Upvotes: 0

Jason McCreary
Jason McCreary

Reputation: 72961

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.

Upvotes: 3

Related Questions