<cfsilence>

T-SQL Dealing with Null Values in an Order By Clause

Posted At : February 21, 2006 8:52 PM | Posted By : todd sharp
Related Categories: SQL

Here's a cool technique that one of my coworkers and I came up with today for an issue I had with using "Order by" in a T-SQL query. Apparently SQL Server handles null values differently than other db's do (such as Oracle). In my case, I had a "rank" column in my table and wanted the results ordered by that rank. Unfortunately, my rank column also had some null values in it (I know there are different schools of thought on null values, but that's another issue for another time!). SQL server handles this by grouping the null values at the top of the result set, kinda like this:

Rank
----
null
null
null
1
2
3

So we kicked around a few ideas that I could use to turn that result set into this:

Rank
----
1
2
3
null
null
null

One idea was to create a procedure to pull the null results into a temp variable table and the non-null values into a separate temp variable table and then join the results into the final result set. Not a bad idea, but it seemed like too much work.

Another idea was to evaluate the rank column within a case statement in the select like so:

SELECT
CASE
when rank is not null then rank
when rank is null then 9999
else null end as rank
FROM blah
ORDER by rank

Not a bad idea, but I still didn't really care for that solution either. The whole idea of hardcoding a value in to represent the null values didn't sit well with me.

The next idea was to use the SQL isnull function within the order by like this:

SELECT rank
FROM blah
ORDER by isnull(rank, 99999)

I liked this solution much better than the previous ideas, but it was still hardcoding the null values. If you're still reading this, trust me, I'll get to the point soon :) - So here was my next idea, how about a subquery to determine the maximum possible rank, then add 1. Pretty dynamic, no hardcoding:

SELECT rank
FROM blah
ORDER by isnull(rank, (SELECT max(rank)+1 FROM BLAH))

A pretty solid solution in my opinion, but my coworker brought up a good point. This solution would run the subquery for each row in the query, thus adding unneccessary proccessing time. So, why not do the query once and set in a variable? Here's the final solution:

--declare a variable to house the maxrank
declare @maxrank int
--store the max rank +1 in that variable
select @maxrank = max(rank)+1 FROM BLAH

SELECT rank
FROM blah
ORDER by isnull(rank, @maxrank)

So there it is. An efficient, dynamic solution to ordering a result set which puts the null values AFTER the non-nulls.

Comments (8) |

Print |

Send |

Code |

del.icio.us |

Linking Blogs | 8556 Views

Comments

[Add Comment]

Helped me out a lot, thanks.

Stupid ms sql server and its sorting.

# Posted By chris | 8/4/06 7:14 AM

Not a bad solution, but why not simply use a constant with the maximum value of the datatype. Considering the datatype can't be more than that value, it assures the correct position.

E.g. if rank is an int, then ISNULL(rank, 2147483647)

(Granted it would still be better if SQL Server had constants, or something like int.maxValue)

# Posted By Halvo | 11/29/06 12:26 PM

Exactly what I was looking for :-)

Thanks!

# Posted By Ralf Eisenreich | 9/17/07 9:16 AM

You guys are miking this rather difficult. Try this:

SELECT rank
FROM blah
ORDER BY (CASE WHEN rank IS NULL THEN 1 ELSE 0 END), rank

# Posted By Tim | 11/27/07 5:50 PM

tim, that's totally brilliant... i knew there had to be a better way to do this.

# Posted By andrew | 5/4/08 11:44 PM

I don't think tim's solution will do exactly what the OP wanted. It's assigning a rank of 0 to all the non-null items... What about the ordering within the non-null items when all have a 0 rank?

# Posted By Jas | 5/21/08 11:09 PM

uh, jas, yeah it does. notice that "rank" on the end of the order by clause? that handles sorting after the nulls have been moved to the end.

# Posted By andrew | 5/22/08 2:43 AM

fantastic solution Tim, thanks. I think this should come up in the msdn site as well.

# Posted By Ska | 8/8/08 12:42 PM

[Add Comment]