I ran across a situation today where I needed to sort a result set on a column that could contain NULL values. By default, all rows with a NULL value in the sorted column are returned first but in this case, I wanted to have all the NULL values show up last in the result set.
Click here to jump to the correct solution or keep reading for a detail explanation of failed attempts at solving this problem.
For example, imagine you have an Employees table as defined below and with the indicated data.
Running a simple query ordering by the nullable DepartmentId column produces the following result (NULL values at the top).
SELECT * FROM Employees ORDER BY DepartmentId
Ordering the result set by DepartmentId descending obviously won’t work as even though the NULL values will appear at the bottom all the other rows will be in descending order as well.
SELECT * FROM Employees ORDER BY DepartmentId DESC
I have seen some people attempt to solve this problem using the following query.
SELECT Id, FirstName, LastName, (CASE WHEN DepartmentId IS NULL THEN 99999 ELSE DepartmentId END) AS DepartmentId FROM Employees ORDER BY DepartmentId
While this will most likely sort the rows as desired, this presents multiple problems. First, you are specifying a DepartmentId of 99999 for rows that really don’t have a DepartmentId and second, you have to ensure that you never have a DepartmentId over 99999 in your Employee records.
You can eliminate the first problem of the query above by doing the following:
SELECT * FROM Employees ORDER BY ISNULL(DepartmentId, 99999)
The ISNULL function will replace a NULL DepartmentId with the value 99999, thus giving the desired ordering while still preserving the NULL value in the result set. But, this still leaves us with the second issue mentioned above.
Both issues can be resolved by a simple case statement in the ORDER BY clause as shown below.
SELECT * FROM Employees ORDER BY (CASE WHEN DepartmentId IS NULL THEN 1 ELSE 0 END), DepartmentId
Here, the result set is first sorted by a temporary column with the value of 1 if the associated row’s DepartmentId value is NULL and 0 if it is not. Doing such will ensure that all records with a NULL DepartmentId appear after records with a non-NULL DepartmentId. The two groups of records (ones with a NULL DepartmentId and ones with a non-NULL DepartmentId) are then sorted by the DepartmentId which ensures that all records with a DepartmentId are sorted correctly.
A simple solution to a random problem.