Bob Schneider
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.