SQL: ORDER BY two columns mixed, not based on priority

Question

I am using mySQL. I have to order contact names by name, but in case there is no name, I order by first name.

It looks like this:

ORDER BY lastname = "", lastname, firstname 

However, this leads to the fact that with the last names appear at the top. The behavior I would like is to mix the first and last name, as if they were from the same field.

Example (imagine these are names):

 A,TZ,GABC 

Versus:

 A A,T B C Z,G 

thanks

Answer 1

Use COALESCE and NULLIF :

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

Answer 2

Try using Coalesce
Note: for this you will not need to store empty names with an empty string (for example, "")

 ORDER BY Coalesce(LastName, FirstName) 

As suggested in the comments By adding FirstName to the order From the list again, you will correctly order two people with the same lastName. Here is an example.

 ORDER BY Coalesce(LastName, FirstName), FirstName 

Answer 3

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

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

It also makes it possible to return fullname to your results based on your sorting logic.

Answer 4

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

more details here