10

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.

16

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
|improve this answer|||||
  • DISTINCT is unnecessary for the IN predicate. And if only one row per category can have the flag of 1, DISTINCT is unnecessary at all. – Andriy M Jul 18 '16 at 17:00
  • @AndriyM correct about the IN query. But the OP has "I want to select all rows that have flag = 1 at least once per category" which makes me think that the DISTINCT is necessary in the other queries. – ypercubeᵀᴹ Jul 19 '16 at 8:20
  • 1
    And in the CROSS APPLY, the SELECT DISTINCT category should probably be more efficient if replaced with SELECT TOP (1) whatever. It would effectively be another way to write an EXISTS subquery. – ypercubeᵀᴹ Jul 19 '16 at 8:28
  • @Andriy This is why I added a note yesterday based on your initial comment: not needed if there is only 1 row with flag = 1. – Julien Vavasseur Jul 19 '16 at 8:45
4

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.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.