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
select * , Row_Number() over(partition by id_out_sta order by max_date_sta desc) rn
from t
| id_out_sta | status_exec | max_date_sta | rn |
|---|---|---|---|
| 1 | 5 | 2021-12-07 | 1 |
| 1 | 1 | 2021-11-28 | 2 |
| 1 | 2 | 2021-11-07 | 3 |
| 2 | 6 | 2021-08-07 | 1 |
| 2 | 2 | 2021-05-12 | 2 |
| 2 | 7 | 2021-04-02 | 3 |
| 3 | 5 | 2021-08-28 | 1 |
| 3 | 2 | 2021-08-05 | 2 |
| 4 | 5 | 2021-04-25 | 1 |
| 4 | 2 | 2021-03-15 | 2 |
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 |
SELECT * FROM (
SELECT DISTINCT ON (id_out_sta)
status_exec,
count(*) over(partition by status_exec)
FROM t
ORDER BY id_out_sta, max_date_sta DESC
) as list
GROUP BY 1, 2
| status_exec | count |
|---|---|
| 5 | 3 |
| 6 | 1 |