sql – select single ID for each group with the lowest value

Consider the following table:

ID      GroupId     Rank
1       1           1
2       1           2
3       1           1
4       2           10
5       2           1
6       3           1           
7       4           5

I need an sql (for MS-SQL) select query selecting a single Id for each group with the lowest rank. Each group needs to only return a single ID, even if there are two with the same rank (as 1 and 2 do in the above table). I’ve tried to select the min value, but the requirement that only one be returned, and the value to be returned is the ID column, is throwing me.

Does anyone know how to do this?

Answer

Use row_number():

select t.*
from (select t.*,
             row_number() over (partition by groupid order by rank) as seqnum
      from t
     ) t
where seqnum = 1;