Handling Sorts with Nulls

Posted November 12, 2007

If you want to sort on a field that may contain NULLs and do not want them to be placed at the beginning, use one of the following methods to ensure NULLs are stuck at the bottom.

Method #1

order by Coalesce([columnName], '')

For example, if the sort column is an integer name rocks, your order by would look like this:

order by Coalesce([rocks], '2147483647')

If it were a datetime:

order by Coalesce([startDate], '9999-12-31 23:59:59.997')

Method #2

order by case when [columnName] is null then 1 else 0 end, [columnName]