ORDER BY with columns that are sometimes empty

Question

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.

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 ...
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.

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 ...
ORDER BY CompanyName , LastName , COALESCE(FirstName,1)

Comments

[Martin Smith]

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.

[aleroot]

I don't know how many fields allow null, the poster should post table structure for a more precise answer.

[Martin Smith]

+1 Doesn't handle blank columns but for that would be best to know RDBMS

[Kajetan Abt]

@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]

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.

[Andriy M]

@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.

[Kajetan Abt]

Martin Smith has the gist of it. This won't order by LastName if CompanyName is NULL or empty.