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

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

回答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)

回答3:

SELECT CASE WHEN CompanyName IS NOT NULL AND CompanyName <> '' THEN CompanyName ELSE '' END, 
  LastName , FirstName FROM ... JOIN ...
ORDER BY LastName, FirstName, CompanyName