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 these results to appear at the end.

In this example, I would like the fourth entry (which starts with the letter C) to be the third. But if I just ORDER, this will happen:

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

In addition, sometimes I get more ordinal columns in several cases, more or less important. This may be relevant.

Additions: either the surname or the company should have a useful line. The name is completely optional. PostgreSQL system (8.4, can migrate up to 9), as well as SQLite. Provider independence would be a plus because there are potential customers who are already using Oracle and SQLServer.

Answer 1

You may need to tweak this to fit your needs, but as 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 be mainly order by the fact that of the three columns, which are not non-zero at first, either by name or by first name and, finally, by name. In my opinion, this order does not make much sense, but YMMV.

Answer 2

You must put COALESCE in ORDER BY for fields that were empty, for example:

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

Answer 3

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