Recently one of the guys I worked with was chatting with me about a problem he had getting the top 1 from the results of a grouped result set, in SQL Server 2005.
We Googled and we found some pretty long winded suggestions that we weren’t happy with.
I think this is a nice, simple, solution, using row_number() over a partition.
-- get top 1 of a group by with RankedTrackResults as ( select *, row_number() over (partition by Age order by TimeInSeconds) as Position from TrackResults ) select * from RankedTrackResults where Position = 1
Have a play with it here:
In the example, the set that I’ve created is meant to be some results for some 10 to 13 year old kids running on a track. It seems to take them about 60 seconds to complete the course.
The SQL will find who came first in each age group (i.e. top 1, grouped by age).