How to get the position of a record in a table (SQL Server)
Posted
by
Peter Siegmann
on Stack Overflow
See other posts from Stack Overflow
or by Peter Siegmann
Published on 2011-05-20T20:54:18Z
Indexed on
2012/04/14
5:29 UTC
Read the original article
Hit count: 274
sql
|sql-server-2008
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.
© Stack Overflow or respective owner
