Postgres 11

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
    ("course_id" varchar(7), "sec_id" int, "semester" varchar(4), "year" int, "count" int);
    
INSERT INTO mytable VALUES
    ('CS-101', 1, 'Fall', 2009, 6),
    ('CS-347', 1, 'Fall', 2009, 2),
    ('PHY-101', 1, 'Fall', 2009, 1);
3 rows affected

INSERT INTO mytable VALUES
    ('AB-42', 1, 'Fall', 2009, 6);

SELECT * FROM mytable
1 rows affected



course_id
sec_id
semester
year
count


CS-101
1
Fall
2009
6

CS-347
1
Fall
2009
2

PHY-101
1
Fall
2009
1

AB-42
1
Fall
2009
6


SELECT
    *,
    rank() OVER (ORDER BY count DESC) 
FROM
    mytable


course_id
sec_id
semester
year
count
rank


CS-101
1
Fall
2009
6
1

AB-42
1
Fall
2009
6
1

CS-347
1
Fall
2009
2
3

PHY-101
1
Fall
2009
1
4


SELECT
    *
FROM (
    SELECT
        *,
        rank() OVER (ORDER BY count DESC) 
    FROM
        mytable
) s
WHERE rank = 1


course_id
sec_id
semester
year
count
rank


CS-101
1
Fall
2009
6
1

AB-42
1
Fall
2009
6
1

course_id	sec_id	semester	year	count
CS-101	1	Fall	2009	6
CS-347	1	Fall	2009	2
PHY-101	1	Fall	2009	1
AB-42	1	Fall	2009	6