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.
Need a home to be homesick
3 months ago
6 comments:
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
It does not return the actual row numbers isnt it, it just number the result set rows.
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
Very Nice, exactly what I have been looking for.
It is useful to try everything in practice anyway and I like that here it's always possible to find something new. :)
It is useful to try everything in practice anyway and I like that here it's always possible to find something new. :)
Post a Comment