Selecting group of records with some NULL value in one of its fields-CodePudding

Having a table such as this one:

season | month | coldest_temp
-----------------------------
winter | Dec   |  -5
winter | Jan   |  -8
winter | Feb   |  NULL
spring | Mar   |  7
spring | Apr   |  10
spring | May   |  15

How could I extract the group of seasons that has some NULL value in the field 'coldest_temp' (in this case the three winter records)? I imagine it must be using GROUP BY and HAVING, but I can't come up with a statement that would work :S

CodePudding user response：

Try this

Select season
From table group by season
having 1= sum(case when coldest_temp is null then 1 else 0 end) ;

Or for all columns try using correlated subquery

Select season, month
From table t where exists (
Select 1 from table where season=t.season and month < t.month and coldest_temp is null)