Selecting a running position of the returned row in MSSQL

Posted by on February 6, 2012 in Database, MSSQL | 0 comments

For some reason this concept is hard to summarize in one line. Basically we’re looking to return how many rows are in the table before the row returned in the result. Sort of like what position the rows is in line. If we have a table like this:

Row: 1,2,3
Row: 2,4,5
Row: 3,6,7

And we want to find out the position of row 2, then it’s fairly simple because the primary key will tell us what order it is. However, if you delete row 1, then row 2 becomes the first row in line. The following sql will tell you how many rows are left in the table before the row you’re returning. This is joining on the same table, but you could expand it for count rows in another table. In that case you wouldn’t need aliases.

SELECT table1.*, (
SELECT COUNT(*)
FROM table as table2
WHERE table12.pk_column< table1.pk_column
) as rowsBefore
FROM table as table1
WHERE someConditionOnTable1
If you enjoyed this post, please consider leaving a comment or subscribing to the RSS feed to have future articles delivered to your feed reader.

Leave a Comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>