SQL Server 2019

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


select * from #y where flag=1
union all
select * from #y
where (select sum(flag) from #y)=0


id
group_id
flag


504
100
1

509
102
1