SQL - Pick one record from group if TRUE, all records if FALSE

Question

I can brute force this, but feel like there's a more efficient way. For each group ID below, I want to return the ONE record flagged as TRUE. Or if NO record in the group is flagged as TRUE, then I want to return ALL the FALSE records. I'm using the latest version of SQL Server.

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

The desired results would return ID's 504 and 509 (the single TRUE record in groups 100 and 102 respectively) and ID's 505, 506, 507 (all records in group 101 because no TRUE record exists in the group).

select * from #y where id in (504, 505, 506, 507, 509)

I tried some things with subqueries and window functions but feel like there's a more straightforward way. Thank you.

Answer 1

It can quite easily be done with an OR condition, as follows:

SELECT *
FROM   #y
WHERE  flag = 1
OR     group_id NOT IN (SELECT group_id FROM #y WHERE flag = 1)

Results:

504 100 1
505 101 0
506 101 0
507 101 0
509 102 1

Working demo: DB Fiddle

Answer 2

A simple approach to this would be (assuming a group_id can only have a single row where flag = 1):

SELECT
   *
FROM table
WHERE flag = 1
UNION ALL
SELECT
   *
FROM table
WHERE group_id NOT IN (SELECT group_id FROM table WHERE flag = 1)

Answer 3

One solution using window functions would be to use Row_number to rank the rows, and Max to determine if no rows in a partition have a flag:

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;

See Demo Fiddle

Result:

Note, if you have more than 1 "true" per group then you can use dense_rank instead.

Answer 4

You can use NOT EXISTS:

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
   );

Or, with RANK() window function:

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;

See the demo.

Answer 5

use union all

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

demo link