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 mytable (
market varchar(20),
certificate_id varchar(20),
audit_ending date
);
insert into mytable values
('K010', 'vwv', '2023-12-10'),
('K010', 'cert1', '2023-12-12'),
('K054', 'vwv', '2023-12-14'),
('K054', 'cert1', '2024-01-20');
CREATE TABLE
INSERT 0 4
select *
from (
select *, row_number() over (partition by market order by audit_ending) as rn
from mytable
) as s
where rn = 1
| market | certificate_id | audit_ending | rn |
|---|---|---|---|
| K010 | vwv | 2023-12-10 | 1 |
| K054 | vwv | 2023-12-14 | 1 |
SELECT 2