Selecting a running position of the returned row in MSSQL
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:
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.
FROM table as table2
WHERE table12.pk_column< table1.pk_column
) as rowsBefore
FROM table as table1