Retrun row numbers from a TSQL query

I came across a situation where I need to get the row numbers together with the result set. And finally found a very tricky way of doing it which I felt worth sharing;

SELECT col1,col2,(SELECT COUNT(*) FROM Table1 T2
WHERE T2.pkid <= T.pkid) AS rownumber
FROM Table1 T

And if you have a where condition it will return the original row number regardless of the no of the filtered rows. Hope this will be helpful to someone.

6 comments:

Buddhika said...

About you TSQL query, why don't you use ROW_NUMBER() OVER function to get the row number? Don't want to go through lots of troubles. So your code will look like;

SELECT col1,col2, ROW_NUMBER OVER (order by col2 desc) as RowNumber FROM Table1

easy

Kaminda Berugoda said...

It does not return the actual row numbers isnt it, it just number the result set rows.

Buddhika said...

No, It returns the sequential row number for the each record. Lets say that if you filtered the recordset and you have 6 rows resulted, then you will see the rownumbers 1-6, if your recordset gave 12 rows, the rownumbers will be 1-12, no problem. This is 2005 inbuild function to get the respective row number

Anonymous said...

Very Nice, exactly what I have been looking for.

Anonymous said...

It is useful to try everything in practice anyway and I like that here it's always possible to find something new. :)

Anonymous said...

It is useful to try everything in practice anyway and I like that here it's always possible to find something new. :)

 
Blog Directory - OnToplist.com