sqlsql-servert-sqlsql-server-2008

How to get first record out of each group from the result retrieved by using group by command


Suppose if my Input is:

ID  GroupID  Qty
1         1  100
2         1  200
3         1  300
4         2  98
5         2  198
6         3  175
7         3  275
8         3  375
9         4  215

Output should be

ID   GroupID    Qty
 1         1    100
 4         2    98
 6         3    175
 9         4    215

Can any one help me how to do it with SQL Server T-SQL query?


Solution

  • declare @T table (ID int, GroupID int, Qty int)
    insert into @T values
    (1, 1, 100),
    (2, 1, 200),
    (3, 1, 300),
    (4, 2, 98),
    (5, 2, 198),
    (6, 3, 175),
    (7, 3, 275),
    (8, 3, 375),
    (9, 4, 215)
    
    ;with cte as
    (
      select
        ID,
        GroupID,
        Qty,
        rank() over(partition by GroupID order by ID) as rn
      from @T
    )  
    select ID, GroupID, Qty
    from cte
    where rn = 1