One of my clients has a reporting system that orders the data by a column that is a date. They wanted the sort to be ascending, but this places columns with a NULL value at the top followed by the data ascending. They wanted the NULL dates to appear at the bottom. To achieve this, you have to use a syntax that I’ve never used before. Here is the original query:

SELECT * from Jobs as j ORDER BY Date ASC

Here is the modified query that makes use of the CASE statement:

SELECT * from Jobs as j ORDER BY CASE WHEN Date IS NULL THEN 1 ELSE 0 END, Date ASC

The revised query does the initial sort based on the result of the CASE statement and then based on the value in the Date field. The CASE statement itself returns a value of 1 if the Date field is NULL and 0 otherwise. The result of this is that all of the rows with data in Date are first, ordered by Date followed by the remaining rows. It works great and produces the exact result that the client was after.

For additional information on the CASE statement, you can check out this Database Journal article.