New Ranking tutorial

This is kinda cool. I currently do not have a need for it as my job does not require me to write new tsql code on a daily basis anymore. However, when I used to write ranking code it was always a LONG and TEDIOUS process not really because of the Ranking code, but mostly because of what it takes to get to the ranking code. ARG! My Ranking routine believe it or not consisted of a small segment of code with a CURSOR!

This is an snippet of code from what I used to use and is actually still in production today

declare tstcur CURSOR FOR SELECT id FROM @temp
declare @rank as integer
declare @tID as integer
OPEN tstcur
FETCH NEXT FROM tstcur
INTO @tID
WHILE @@FETCH_STATUS = 0
BEGIN

select @rank = count(*)+1 FROM @temp WHERE final_score > (select final_score from @temp where id = @tID)
insert into @temp (id, Division, responses, max_points, total_points, final_score, rank)
select id, Division, 0, max_points, total_points, final_score, 0 from @temp where id = @tID
update @temp set rank = @rank where id = @tID

FETCH NEXT FROM tstcur INTO @tID
END
CLOSE tstcur
DEALLOCATE tstcur

as you can see it's not really elegant but it DID get the job done. IF you need this type of code you may certainly reproduce it for your own Sql Server 2000 needs.

I do prefer the new method of Ranking using Sql Server 2005 (or 2008). That is the Ranking function built right into the TSQL language so the above code would look like this:

SELECT RANK() OVER (
ORDER BY final_score) AS [Rank],
id, Division, responses, max_points, total_points, final_score
FROM @temp

I quite like the non-cursor solution, and if you've read other articles by me you'll know that I'm not at all a fan of cursors (they're just plain evil!) :)

For a full tutorial on how to use the new Ranking feature... check out
Ranking Functions in Sql Server 2005 and 2008