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:
Post a Comment