Order By With Columns That Are Sometimes Empty

My SQL looks something like this: SELECT CompanyName , LastName , FirstName FROM ... JOIN ... ORDER BY CompanyName , LastName , FirstName Now the problem is that column A is somet

Solution 1:

You might have to tweak this to fit your needs, but the way I understand it, this should do the trick:

SELECT CompanyName , LastName , FirstName FROM ... JOIN ...
ORDERBYCOALESCE(CompanyName , LastName, FirstName),
         COALESCE(LastName, FirstName),
         FirstName

This will mainly order by whichever of the three columns that are not null first, then either by last- or first name, and lastly by first name. In my opinion, this ordering won't make much sense, but YMMV.

Solution 2:

You should put a COALESCE in the ORDER BY for the fields that are subjected to be null, so for example :

SELECT CompanyName , LastName , FirstName FROM ... JOIN ...
ORDERBY CompanyName , LastName , COALESCE(FirstName,1)

Solution 3:

SELECTCASEWHEN CompanyName ISNOTNULLAND CompanyName <>''THEN CompanyName ELSE''END, 
  LastName , FirstName FROM ... JOIN ...
ORDERBY LastName, FirstName, CompanyName