Alex Rivera | Logout

ORDER BY with columns that are sometimes empty

Asked 2011-11-14T11:33:43.183
14

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.

Edit
Report

1 Answer

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)
answered 2011-11-14T11:40:12.360

Your Answer