By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
with cte1 as
(
select 1 as id, 'John' as name, 500 as score, 2016 as yr
union all
select 2, 'Kim', 900 , 2015
union all
select 3 , 'Ren', 300 , 2016
union all
select 4 , 'John' , 600 , 2015
union all
select 5, 'Kim' , 200 , 2016
union all
select 6 , 'Ren' , 200 , 2016
)
select * from cte1 a where score in
(select max(score) from cte1 b where a.yr=b.yr and b.yr=2016)
and a.yr=2016
| id | name | score | yr |
|---|---|---|---|
| 1 | John | 500 | 2016 |