how to select top 1 from each group after order by

Question

I have 3 columns/fields as part of a generic list: strID, seq, unit
there are a bunch of strIDs that belong to the same unit and they each have a different seq. I'm interested in the row that has the minimum seq per unit(the leader of the group). How would I accomplish this with a LINQ query (a tsql query would also be fine)?

Following is example data:

  strID, seq, unit
    aaa, 3, 1
    bbb, 2, 1
    ccc, 4, 1
    ddd, 8, 2
    eee, 15,2
    fff, 7, 2

My query would get me the following:

leaderID, unit
bbb, 1
fff, 2

Answer 1

source
  .GroupBy(row => row.unit)
  .Select(g => g.OrderBy(row => row.seq).First());

Answer 2

In T-SQL, you would use

SELECT leaderID, unit
FROM (
    SELECT strID, unit, row_number() over(partition by unit order by seq asc) as ranking_within_unit
    FROM t
     )
WHERE ranking_within_unit = 1

The window functions (OVER()) are made for this purpose.