SQL: Order By with NULL Values Last


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.

The Solution

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.

Advertisements
Posted in Sorting, SQL. Tags: , . 4 Comments »

4 Responses to “SQL: Order By with NULL Values Last”

  1. Ram Says:

    Thanks for your detailed and clear explanation !!

    • Nick Olsen Says:

      Anytime!

  2. jen Says:

    Perfect! Thank you!!!

  3. Tom Says:

    Oracle SQL Reference Manual lists another solution for your problem in the syntax of the ORDER BY command:
    ORDER [ SIBLINGS ] BY
    { expr | position | c_alias }
    [ ASC | DESC ]
    [ NULLS FIRST | NULLS LAST ]
    [, { expr | position | c_alias }
    [ ASC | DESC ]
    [ NULLS FIRST | NULLS LAST ]
    ]…

    This has been available at least since version 9.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: