Fun With ORDER BY

Ever notice how when you ask for results to be ordered by a particular field, that all the null values come at the start of the list?

What if you want the null values to come last, so that you get:

It's worth remembering that you can ORDER BY any boolean expression:

SELECT MyField FROM MyTable
ORDER BY NOT MyField IS NULL, MyField

Note that not all database systems support this syntax. In any SQL-92 compliant system, this query can be rephrased as:

SELECT MyField FROM MyTable
ORDER BY CASE WHEN MyField IS NULL THEN 1 ELSE 0 END,
MyField

Notice that I have also ordered by MyField, so that after making sure all the non-null values are listed first, those values are listed alphabetically.

Now I really like New Zealand, so maybe I want them to come first on my list, with the rest of the world in alphabetical order (that would make more sense if I worked for a company in New Zealand, but in this case it is just because I am insane).

SELECT MyField FROM MyTable
ORDER BY NOT MyField IS NULL,
MyField='New Zealand', MyField

Or, using the CASE syntax:

SELECT MyField FROM MyTable
ORDER BY CASE WHEN MyField IS NULL THEN 1 ELSE 0 END,
CASE WHEN MyField='New Zealand' THEN 0 ELSE 1 END,
MyField

One more thing: Although I titled this tip "Fun with ORDER BY", it can also be applied to GROUP BY.