How to get the position of a record in a table (SQL Server)

Following problem:

I need to get the position of a record in the t开发者_如何学运维able. 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.

Tables don't have 'position'. Rows in a table (=set) are identified by their primary key value. Only result rows have 'position' which can be deterministic when a ORDER BY clause is present. Assuming that tables (=sets) have a position will lead only to problems and is the wrong mind set.

You can use row_number() to "label" rows. You've got to specify a way to order the rows:

select  row_number() over (order by id) as PositionInTable
,       *
from    YourTable

If performance is an issue, you could store the position in a new column:

update  yt1
set     PositionInTable = rn
from    YourTable yt1
join    (
        select  row_number() over (order by id) as rn
        ,       id
        from    YourTable
        ) yt2
on      yt1.id = yt2.id

With an index on PositionInTable, this would be lightning fast. But you would have to update this after each insert on the table.

Tables are [conceptually] without order. Unless you specify ORDER BY in a select statement to order a results set, results may be returned in any order. Repeated executions of the exact same SQL may return the results set in different orders fro each execution.

To get the row number in a particular result set, use the row_number() function:

select row = row_number() over( order by id ) , *
from sysobjects

This will assign a row number to each row in sysobjects as if the table were ordered by id.

A simple way to do this without having to use ROW_NUMBER would be to simply count how many rows in the table have an index less or equal to the selected index, this would give the row number.

SELECT COUNT(*) FROM YourTable WHERE ID <= 4 -- Frankie Boy, Result = 3

This may not be the most efficient way to do it for your particular scenario, but it's a simple way of achieving it.