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 (
"loc" VARCHAR(1),
"id" VARCHAR(4),
"filter" VARCHAR(3),
"p1" FLOAT
);
INSERT INTO t
("loc", "id", "filter", "p1")
VALUES
('A', 'ABC1', 'GHY', '55.6'),
('A', 'DFT1', 'FGH', '67.8'),
('B', 'HJH5', 'GHY', '67'),
('C', 'HKL', 'BHY', '78'),
('B', 'GTY', 'FGH', '60');
5 rows affected
select filter
,id
,loc
,p1
,m_id
,m_loc
,m_p1
from
(with t2 as
(select row_number () over( partition by filter order by filter desc) as rn
,*
from t)
select rn,filter, id, loc, p1
,lead(id) over( partition by filter order by filter) as m_id
,lead(loc) over( partition by filter order by filter) as m_loc
,lead(p1) over( partition by filter order by filter) as m_p1
from t2) t
where rn=1
| filter | id | loc | p1 | m_id | m_loc | m_p1 |
|---|---|---|---|---|---|---|
| BHY | HKL | C | 78 | null | null | null |
| FGH | DFT1 | A | 67.8 | GTY | B | 60 |
| GHY | ABC1 | A | 55.6 | HJH5 | B | 67 |