Outside of SQL it would be quite easy to number the rows returned from query, but to do so inside of the SQL is a bit more difficult. I recently wanted to be able to number the rows returned from a query in a stored procedure. I am using SQL Server 2000, so I cannot use row_number().
I was going to ask my go to SQL guy, Gregg Stark, how to do this, but while in the process of asking him for a good method of doing this, I came up with the solution. My cool trick for numbering the rows returned from a query is useful because I don't actually want to return these rows to other code. I want to store them back into my database ordered differently than the numbering, and using a table variable will allow me to do exactly what I am trying to do.
First we need to create a table variable. We will define that table as having an identity column plus any other columns we happen to need. We then insert into that table all of the values returned from our query.
declare @t table (Rank int identity(1,1), UserName nvarchar(256), Points int)
insert into @t
select UserName, sum(PointsEarned) as Points
from UserRecognition
group by UserName
After running this part we have created a table variable which contains the information we want. We can then select data out of this as if it were a normal table. In this example I am able to obtain a ranking for my users based on their earned points. This lets me store that ranking without having them stored in ranking order.
It is obviously much easier to do this in SQL 2005, but this is a very cool trick for those of us using SQL Server 2000. Let me know what you think, and if you have any cool improvements.
Comments