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