CODEWITHSUNDEEP
t-sqlgroup-by
user1589188
user1589188

Reputation: 5736

T-SQL group by and filter for all

Please edit my question for a better title (and remove this line)

Hi I have a table

id|Col_A|Col_B|
--|-----|-----|
 1|  A  | NULL|
 2|  A  |  a  |
 3|  B  |  a  |
 4|  C  | NULL|
 5|  C  | NULL|

I want to select only those rows where all Col_B is NULL group by Col_A (i.e. only C should be returned in this example)

Tried

SELECT Col_A FROM MYTABLE WHERE Col_B IS NULL GROUP BY Col_A

which gives me A and C. Also tried

SELECT Col_A, Col_B FROM MYTABLE GROUP BY Col_A, Col_B HAVING Col_B IS NULL

which also gives me A and C.

How do you write a T-SQL query to select a group only when all match the criteria? Thank you

Upvotes: 0

Views: 159

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239764

This seems to work:

declare @t table (id int,Col_A char(1),col_b char(1))
insert into @t(id,Col_A,Col_B) values
(1,'A',NULL),
(2,'A', 'a'  ),
(3,'B', 'a'  ),
(4,'C',NULL),
(5,'C',NULL)

select Col_A from @t group by Col_A having MAX(Col_B) is null

Because MAX (or MIN) can only return NULL if all of the inputs were NULL.

Result:

Col_A
-----
C

Similarly, if you wanted to check that all of the values where all some specific, non-NULL value, you could check that by first confirming the the MIN() and MAX() of that column were equal and then checking either one of them against the sought for value:

select Col_A from @t group by Col_A
having MAX(Col_B) = MIN(Col_B) and
       MIN(Col_B) = 'a'

Would return just B, since that's the only group where all input values are equal to a.

Upvotes: 1

Related Questions