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 tab
AS
SELECT 1 AS id, 11 AS grp, 1 AS factor
UNION ALL SELECT 2 ,11, 5
UNION ALL SELECT 4 ,11, 2
UNION ALL SELECT 5 ,12, 3
UNION ALL SELECT 6 ,12, 2
UNION ALL SELECT 7 ,13, 4
UNION ALL SELECT 8 ,13, 1
UNION ALL SELECT 9 ,13, 8
UNION ALL SELECT 10 ,14, 6
9 rows affected
SELECT DISTINCT ON(grp) grp, factor, id
FROM tab
ORDER BY grp, factor DESC;
| grp | factor | id |
|---|---|---|
| 11 | 5 | 2 |
| 12 | 3 | 5 |
| 13 | 8 | 9 |
| 14 | 6 | 10 |