Sort Data with NULLs at the Bottom in Microsoft SQL Server
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.
13 Jan 2009 03:18 pm Chris 1 comment
I’ve used ORDER BY IsNull(Date, ’12/31/9999′)