Select all rows based on a condition present in a single row

December 9, 2022

Given a table that looks like this:

id	year_month	count
1	2022_01	9
1	2022_02	5
1	2022_03	4
2	2022_01	11
2	2022_02	13
2	2022_03	10
3	2022_01	3
3	2022_02	15
3	2022_03	4

A query is needed that extracts all rows with the same id if any of those rows have a count value >= 10.

The expected result would look like this:

id	year_month	count
2	2022_01	11
2	2022_02	13
2	2022_03	10
3	2022_01	3
3	2022_02	15
3	2022_03	4

So basically it will select all rows with id 2 because all counts are >= 10, but it will also select all rows with id 3 because the entry for 2022_02 has a count higher than 10.

A simple SELECT * FROM table WHERE count >= 10 doesn’t do the job of course.

I’m not even sure how to search for this…

>Solution :

with cte as (
select id from table_a group by id having max(count) > 10)
select t.id,
       t.year_month,
       t.count
  from table_a t
  join cte
 using (id);