select rows having condition met for group (without temporary table)

Question

Having the table with 3 columns:

ID  category    flag
1       A       1
2       A       0
3       A       0
4       B       0
5       C       0

I want to select all rows that have flag = 1 at least once per category.

Expected results:

ID  category    flag
1       A       1
2       A       0
3       A       0

It can be solved using a temporary table like this:

select ID into #tempTable from someTable where flag = 1
select * from someTable join #tempTable on someTable.ID = #tempTable.ID

But I'd prefer a solution with grouping, which I struggle to came up with. Any help will be appreciated.

Answer 1

GROUP BY cannot be used alone because it only returns 1 row per group (category).

---

• You can use a sub query with flag = 1 and INNER JOIN:

  SELECT d1.ID, d1.category, d1.flag
  FROM data d1
  INNER JOIN (
      SELECT DISTINCT category FROM data WHERE flag = 1
  ) d2 
      ON d2.category = d1.category ;
  

• You can use the EXISTS clause:

  SELECT d.ID, d.category, d.flag
  FROM data d
  WHERE EXISTS (
      SELECT 1 FROM data WHERE flag = 1 AND category = d.category
  ) ;   
  

• You can use IN clause (although EXISTS is better):

  SELECT d.ID, d.category, d.flag
  FROM data d
  WHERE d.category IN (SELECT category FROM data WHERE flag = 1) ;
  

• You can also use CROSS APPLY with a sub query on flag = 1:

  SELECT d.ID, d.category, d.flag
  FROM data d
  CROSS APPLY (
      SELECT TOP (1) category 
      FROM data 
      WHERE flag = 1 AND category = d.category
  ) ca ;
  

DISTINCT aren't needed if, for each category, only 1 row can have flag = 1.

Output:

ID  category    flag
1       A       1
2       A       0
3       A       0

Answer 2

Assuming that Flag is a BIT column or an INT that takes only 0 and 1 as values, this could be achieved using windowed functions as well. For instance:

DECLARE @Test TABLE
(
  ID INT
  , Category VARCHAR(1)
  , Flag BIT
);

INSERT INTO @Test (ID, Category, Flag)
VALUES (1, 'A', 1)
  , (2, 'A', 0)
  , (3, 'A', 0)
  , (4, 'B', 0)
  , (5, 'C', 0);

SELECT T.ID
  , T.Category
  , T.Flag
FROM (
  SELECT ID
    , Category
    , Flag
    , MAX(CAST(Flag AS TINYINT)) OVER(PARTITION BY Category) AS MaxFlag
  FROM @Test
  ) AS T
WHERE T.MaxFlag = 1;

That's the output:

ID Category Flag  
-- -------- ----- 
1  A        True  
2  A        False 
3  A        False 

This will find highest Flag for each category in your table, in your case it's probably true/false only and pick one who has true(1) only.

The conversion to TINYINT is needed because MAX doesn't accept a BIT argument.