Friday, March 12, 2021

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


Suppose if Input is:
ID GROUP ID  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 GROUP ID  QTY
1 1 100
4 2 98
6 3 175
9 4 215

 Solution:
 Get first record out of each group from the result:
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

No comments: