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 MY_TABLE (
id VARCHAR(50),
year VARCHAR(50)
);
INSERT INTO MY_TABLE (id, year) VALUES ('123', '2010');
INSERT INTO MY_TABLE (id, year) VALUES ('123', '2011');
INSERT INTO MY_TABLE (id, year) VALUES ('123', '2012');
INSERT INTO MY_TABLE (id, year) VALUES ('124', '2011');
INSERT INTO MY_TABLE (id, year) VALUES ('124', '2012');
INSERT INTO MY_TABLE (id, year) VALUES ('124', '2013');
INSERT INTO MY_TABLE (id, year) VALUES ('125', '2011');
INSERT INTO MY_TABLE (id, year) VALUES ('125', '2012');
INSERT INTO MY_TABLE (id, year) VALUES ('125', '2013');
INSERT INTO MY_TABLE (id, year) VALUES ('127', '2011');
INSERT INTO MY_TABLE (id, year) VALUES ('127', '2012');
INSERT INTO MY_TABLE (id, year) VALUES ('127', '2015');
INSERT INTO MY_TABLE (id, year) VALUES ('126', '2019');
WITH CTE AS (
SELECT id, year, ROW_NUMBER() OVER (PARTITION BY id ORDER BY year) AS rn
FROM MY_TABLE
),
CTE3 AS (
select id
, year
, rn
, max(rn) over (partition by id) as MaxRN
from cte
),
CTE4 AS (
| combination | freq |
|---|---|
| 2011,2012,2015 | 1 |
| 2011,2012,2013 | 2 |
| 2010,2011,2012 | 1 |
| 2019 | 1 |
SELECT 4