ORDER BY with columns that are sometimes empty

sql sql-order-by coalesce
Kajetan Abt picture Kajetan Abt · Nov 14, 2011 · Viewed 12.6k times · Source

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.

Answer

PatrikAkerstrand picture PatrikAkerstrand · Nov 14, 2011

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.

Related questions

SQL multiple column ordering

I am trying to sort by multiple columns in SQL, and in different directions. column1 would be sorted descending, and column2 ascending. How can I do this?

Read More
How does MySQL process ORDER BY and LIMIT in a query?

I have a query that looks like this: SELECT article FROM table1 ORDER BY publish_date LIMIT 20 How does ORDER BY work? Will it order all records, then get the first 20, or will it get 20 records and order them by …

Read More
How to order by with union in SQL?

Is it possible to order when the data is come from many select and union it together? Such as Select id,name,age From Student Where age < 15 Union Select id,name,age From Student Where Name like "%a%" How …

Read More