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: 243

Filed under:
|

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

Related posts about sql

Related posts about sql-server-2008