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?.
create table t (id_out_sta int, status_exec int, max_date_sta date);
insert into t select 1, 2, '2021-11-07';
insert into t select 1, 1, '2021-11-28';
insert into t select 1, 5, '2021-12-07';
insert into t select 2, 7, '2021-04-02';
insert into t select 2, 2, '2021-05-12';
insert into t select 2, 6, '2021-08-07';
insert into t select 3, 2, '2021-08-05';
insert into t select 3, 5, '2021-08-28';
insert into t select 4, 2, '2021-03-15';
insert into t select 4, 5, '2021-04-25';
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
with s as (
select * , Row_Number() over(partition by id_out_sta order by max_date_sta desc) rn
from t
)
select status_exec, Count(*) "Count"
from s
where rn=1
group by status_exec
| status_exec | Count |
|---|---|
| 5 | 3 |
| 6 | 1 |