how to select top 1 from each group after order by

Question

Hi all, 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

Try this on for size:

select x.strID, a.unit
  from (select unit, MIN(seq) as seq from myTable group by unit) a
  join myTable x 
    on a.unit = x.unit and a.seq = x.seq

Answer 2

Try this..

set nocount on
go
WITH MyTable AS
(  
SELECT 'aaa' [strID], 3 seq, 1 unit
UNION SELECT 'bbb', 2, 1
UNION SELECT 'ccc', 4, 1
UNION SELECT 'ddd', 8, 2
UNION SELECT 'eee', 15,2
UNION SELECT 'fff', 7, 2
)
,
MinSequence AS
(
    SELECT Unit, Min (Seq) MinSequence
    FROM MyTable
    Group BY Unit
)
SELECT MT.*
FROM MyTable MT
    JOIN MinSequence MS
        ON MT.Unit = MS.Unit
        AND MT.Seq = MS.MinSequence

results in

strID seq         unit
----- ----------- -----------
bbb   2           1
fff   7           2

Answer 3

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.

Answer 4

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