Link to home
Start Free TrialLog in
Avatar of Bob Schneider
Bob SchneiderFlag for United States of America

asked on

Find position of a record in a SQL Server Query

I need to find the position of a record in a query as efficiently as possible.  Specifically, I am querying a list of race participants in order of finish time and looking for a specific finisher and their place in the list (ie: 15th place).  What is the best way to do that?
Avatar of ste5an
ste5an
Flag of Germany image

There is no such thing.. We have rows in a result set. But as it is a set, it has no position.

You can calculate a "position" (ROW_NUMBER(), RANK() or DENSE_RANK(), see Ranking) for a set by specifying an order on it to do so. E.g.

SELECT T.* ,
       ROW_NUMBER() OVER ( ORDER BY T.FinishTime ) AS RowNumber ,
       RANK() OVER ( ORDER BY T.FinishTime ) AS RankNumber ,
       DENSE_RANK() OVER ( ORDER BY T.FinishTime ) AS DenseRankNumber
FROM   yourTable T;

Open in new window


Caveat: Assuming that you have some sports race here, calculating the place is incorrect. Almost any sports knows referee decisions for the places. Thus finish time does not imply place directly.
SOLUTION
Avatar of Rikin Shah
Rikin Shah
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial