11

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 sometimes empty (either as NULL or ""), and I don't want all those results to turn up in the end.

In the example, I'd like to have the fourth entry (which starts with a C) to be the third. But if I just ORDER BY, this happens:

Avagax Bauer Frank
Bele AG Smith John
Mork AG Baggins Frodo
Chen Jun

In addition, I sometimes have more order-by columns in a few cases, either more or less important. This might be relevant.

Addendums: Either last name or company must have a useful string. First name is completely optional. The system is PostgreSQL (8.4, might migrate to 9), and also SQLite. Vendor-independence would be a plus, because there are potential customers already running Oracle and SQLServer.

Kajetan Abt
  • 1,505
  • 3
  • 15
  • 28

3 Answers3

24

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 ...
ORDER BY COALESCE(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.

PatrikAkerstrand
  • 45,315
  • 11
  • 79
  • 94
  • +1 Doesn't handle blank columns but for that would be best to know RDBMS – Martin Smith Nov 14 '11 at 11:50
  • @Martin Smith: I have just implemented your former solution which works well. It's PostgreSQL and SQLite (and I'd like to not kill MySQL support either). I'll try this too. If necessary, I can work around the NULL-issue (by filling in lots of empty strings), but I'd prefer something that works in both cases. – Kajetan Abt Nov 14 '11 at 11:54
  • 2
    @Kdansky: Try wrapping the COALESCE arguments except the last ones (i.e. except `FirstName`) into `NULLIF`: `… ORDER BY COALESCE(NULLIF(CompanyName, ''), NULLIF(LastName, ''), FirstName), COALESCE(NULLIF(LastName, ''), FirstName), FirstName`. – Andriy M Nov 14 '11 at 12:19
  • Which versions of MYSQL supports COALESCE in order by clause? – Gaurav Patil Mar 03 '23 at 19:21
1

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 ...
ORDER BY CompanyName , LastName , COALESCE(FirstName,1)
aleroot
  • 71,077
  • 30
  • 176
  • 213
  • 1
    That won't work. In the case that the company name is null or blank it looks like the OP wants to order by `LastName` from the example in the question. – Martin Smith Nov 14 '11 at 11:42
  • I don't know how many fields allow null, the poster should post table structure for a more precise answer. – aleroot Nov 14 '11 at 11:48
  • Martin Smith has the gist of it. This won't order by LastName if CompanyName is NULL or empty. – Kajetan Abt Nov 14 '11 at 12:50
0
SELECT CASE WHEN CompanyName IS NOT NULL AND CompanyName <> '' THEN CompanyName ELSE '' END, 
  LastName , FirstName FROM ... JOIN ...
ORDER BY LastName, FirstName, CompanyName
kol
  • 27,881
  • 12
  • 83
  • 120
  • While this might work, it's not practical, as the query is dynamically generated. Having to throw in a CASE like that before getting to ORDER-BY would be problematic. I didn't state that, sorry. – Kajetan Abt Nov 14 '11 at 11:59