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 t1.filter
,max(t1.id) as id
,max(t1.loc) as loc
,max(t1.p1) as p1
,min(t2.id) as m_id
,min(t2.loc) as m_loc
,min(t2.p1) as m_p1
from t as t1 left join t as t2 on t2.filter = t1.filter and t2.id <> (t1.id)
group by t1.filter
| filter | id | loc | p1 | m_id | m_loc | m_p1 |
|---|---|---|---|---|---|---|
| BHY | HKL | C | 78 | null | null | null |
| FGH | GTY | B | 67.8 | DFT1 | A | 60 |
| GHY | HJH5 | B | 67 | ABC1 | A | 55.6 |