SQL Server 2017

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

SELECT t1.* 
FROM #y t1
WHERE t1.flag = 1
   OR NOT EXISTS (
     SELECT * 
     FROM #y t2
     WHERE t2.group_id = t1.group_id AND t2.flag = 1
   );


id
group_id
flag


504
100
1

505
101
0

506
101
0

507
101
0

509
102
1


WITH cte AS (
  SELECT *, RANK() OVER (PARTITION BY group_id ORDER BY flag DESC) 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