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...
CodePudding user response:
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);