select rows having condition met for group (without temporary table)
GROUP BY
cannot be used alone because it only returns 1 row per group (category
).
You can use a sub query with
flag = 1
andINNER 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 (althoughEXISTS
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 onflag = 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
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.