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

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.

CodePudding user response：

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)

CodePudding user response：

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:

Working demo:

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

CodePudding user response：

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.

CodePudding user response：

use union all

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

demo link

CodePudding user response：

this link may help you in this : Using If else in SQL Select statement I think you can use If Else in this query. I wish this link helps you.