By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select 500 id, 100 group_id, 0 flag into #y
union select 501, 100, 0
union select 502, 100, 0
union select 503, 100, 0
union select 504, 100, 1
union select 505, 101, 0
union select 506, 101, 0
union select 507, 101, 0
union select 508, 102, 0
union select 509, 102, 1
union select 510, 102, 0
11 rows affected
with cte as (
select *,
case when
Max(flag) over(partition by group_id) =0 then 1
else
Row_Number() over(partition by group_id order by flag desc)
end rn
from #y
)
select Id, Group_id, Flag
from cte
where rn=1;
| Id | Group_id | Flag |
|---|---|---|
| 504 | 100 | 1 |
| 505 | 101 | 0 |
| 506 | 101 | 0 |
| 507 | 101 | 0 |
| 509 | 102 | 1 |