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?.
WITH yourTable AS (
SELECT 1 AS id, 'A001' AS fkey, 1 AS srno UNION ALL
SELECT 2, 'A001', 2 UNION ALL
SELECT 3, 'A002', 1 UNION ALL
SELECT 4, 'A003', 1 UNION ALL
SELECT 5, 'A002', 2
)
SELECT DISTINCT ON (fkey) id, fkey, srno
FROM yourTable
ORDER BY fkey, srno DESC;
| id | fkey | srno |
|---|---|---|
| 2 | A001 | 2 |
| 5 | A002 | 2 |
| 4 | A003 | 1 |
SELECT 3