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 (
id INT ,
period_type VARCHAR(20) ,
period INT ,
commission INT );
INSERT INTO mytable VALUES
('123456','Daily','10','10'),
('123456','Monthly','3','11'),
('123456','Daily','5','12'),
('123456','Monthly','1','13'),
('123457','Monthly','6','14'),
('123457','Daily','5','15'),
('123457','Monthly','9','16');
CREATE TABLE
INSERT 0 7
SELECT jsonb_agg(m)
FROM (
SELECT
m.id,
m.period_type,
m.period,
m.commission
FROM (
SELECT
m.*,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY period_type DESC, period DESC) AS rn
FROM mytable m
) m
WHERE rn = 1
) m;
| jsonb_agg |
|---|
| [{"id": 123456, "period": 3, "commission": 11, "period_type": "Monthly"}, {"id": 123457, "period": 9, "commission": 16, "period_type": "Monthly"}] |
SELECT 1