Select top 1 of a group by in SQL Server

by EvanJPalmer

the-fying-v-d2-1

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:

http://sqlfiddle.com/#!3/082df/18

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).

Advertisements