Postgres 12

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 table1
    ("id" int, "group" varchar(6), "date" date)
;
INSERT INTO table1
    ("id", "group", "date")
VALUES
    (1, 'group1', '2011-11-24'),
    (2, 'group2', '2011-08-20'),
    (3, 'group1', '2011-03-11'),
    (4, 'group3', '2011-05-05'),
    (5, 'group3', '2012-01-01'),
        (6, 'group4', '2011-05-05'),
    (7, 'group4', '2012-01-01')
;

CREATE TABLE



INSERT 0 7


WITH CTE AS (SELECT
"id", "group", "date"
,MAX("date") OVER (PARTITION BY "group") As maxdategroup
FROM
table1)
SELECT "id", "group", "date" FROM cTE ORDER BY maxdategroup DESC,"group" ,  "date" DESC


id
group
date


5
group3
2012-01-01

4
group3
2011-05-05

7
group4
2012-01-01

6
group4
2011-05-05

1
group1
2011-11-24

3
group1
2011-03-11

2
group2
2011-08-20



SELECT 7


select id, "group", date
from table1
order by max(date) over (partition by "group") desc, "group", date desc ;


id
group
date


5
group3
2012-01-01

4
group3
2011-05-05

7
group4
2012-01-01

6
group4
2011-05-05

1
group1
2011-11-24

3
group1
2011-03-11

2
group2
2011-08-20



SELECT 7

id	group	date
5	group3	2012-01-01
4	group3	2011-05-05
7	group4	2012-01-01
6	group4	2011-05-05
1	group1	2011-11-24
3	group1	2011-03-11
2	group2	2011-08-20