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 temp TABLE lve
("fin" timestamptz, "vehiculo" int, "equipo" int)
;
INSERT INTO lve
("fin", "vehiculo", "equipo")
VALUES
('2021-12-01 00:00:00', 1, 888),
(NULL, 3, 888),
('2021-05-01 00:00:00', 2, 888),
('2021-11-05 00:00:00', 10, 333),
(NULL, 9, 333),
('2021-09-05 00:00:00', 5, 333)
;
CREATE TABLE
INSERT 0 6
select fin, vehiculo, equipo
from (
select fin, vehiculo, equipo
, row_number() over (partition by equipo order by fin desc) as rn
from lve
where fin is not null
) as t
where rn = 1;
| fin | vehiculo | equipo |
|---|---|---|
| 2021-11-05 00:00:00+00 | 10 | 333 |
| 2021-12-01 00:00:00+00 | 1 | 888 |
SELECT 2