Search code examples
sqlimpala

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

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);