Archive note: originally published November 12, 2007, and retained with one correction recorded
here. Method #1 was written as Coalesce([columnName], ''), which replaces NULL with a value that
sorts near the beginning rather than the end - the opposite of what this post sets out to do - and
its typed examples quoted their sentinels. It now shows correctly typed sentinels instead. Method
#2 works as originally written and is still the one to reach for by default. The current version of
this advice is in Eleven SQL Server Checks, Ordered by Return on the Hour Spent.
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
Coalesce the column to a correctly typed sentinel at the far end of its type’s range. For example,
if the sort column is an integer named rocks, your order by would look like this:
order by
Coalesce([rocks], 2147483647)If it were a datetime2(7):
order by
Coalesce([startDate], Convert(datetime2(7), '9999-12-31T23:59:59.9999999'))Method #2
order by
case
when [columnName] is null
then 1
else 0
end,
[columnName]Method #2 is the one to reach for by default - it doesn’t require knowing the type’s maximum, and it doesn’t break when a real row legitimately holds the sentinel value.
Filed alongside the rest of the query-shape advice in Eleven SQL Server Checks, Ordered by Return on the Hour Spent.