How to get the position of a record in a table (SQL Server)
- by Peter Siegmann
Following problem:
I need to get the position of a record in the table. Let's say I have five record in the table:
Name: john doe, ID: 1
Name: jane doe, ID: 2
Name: Frankie Boy, ID: 4
Name: Johnny, ID: 9
Now, "Frankie Boy" is in the third position in the table. But How to get this information from the SQL server? I could count IDs, but they are not reliable, Frankie has the ID 4, but is in the third position because the record with the ID '3' was deleted.
Is there a way? I am aware of ROW_RANK but it would be costly, because I need to select basically the whole set first before I can rank row_rank them.
I am using MS SQL Server 2008 R2.