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 movie (
id int PRIMARY KEY
, yr int
);
CREATE TABLE actor (
id int PRIMARY KEY
, name text NOT NULL -- assuming name unique?
);
CREATE TABLE casting (
movieid int REFERENCES movie
, actorid int REFERENCES actor
, PRIMARY KEY (movieid, actorid)
);
INSERT INTO actor VALUES
(1, 'Buster Keaton')
, (2, 'John Travolta')
, (3, 'Harvey Keitel')
;
INSERT INTO movie VALUES
( 1, '1976')
, ( 2, '1976')
, ( 3, '1977')
, ( 4, '1977')
, ( 5, '1978')
, ( 6, '1978')
, ( 7, '1981')
, ( 8, '1981')
, ( 9, '1994')
, (10, '1994')
, (11, '1989')
, (12, '1999')
;
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 3
INSERT 0 12
INSERT 0 14
-- Postgres 13: WITH TIES
SELECT m.yr, count(*) AS movie_count
FROM casting c
JOIN movie m ON c.movieid = m.id
WHERE c.actorid = (SELECT id FROM actor WHERE name = 'John Travolta')
GROUP BY m.yr
ORDER BY count(*) DESC -- can't sort by year for this
FETCH FIRST 1 ROWS WITH TIES;
| yr | movie_count |
|---|---|
| 1976 | 2 |
| 1977 | 2 |
| 1978 | 2 |
| 1981 | 2 |
| 1994 | 2 |
SELECT 5
-- Older versions: with rank() in subquery
SELECT yr, movie_count
FROM (
SELECT m.yr, count(*) AS movie_count
, rank() OVER (ORDER BY count(*) DESC) AS rnk
FROM casting c
JOIN movie m ON c.movieid = m.id
WHERE c.actorid = (SELECT id FROM actor WHERE name = 'John Travolta')
GROUP BY m.yr
) sub
WHERE rnk = 1
ORDER BY yr; -- optionally sort by year
| yr | movie_count |
|---|---|
| 1976 | 2 |
| 1977 | 2 |
| 1978 | 2 |
| 1981 | 2 |
| 1994 | 2 |
SELECT 5
-- There would be more year, but with fewer movies:
SELECT m.yr, count(*) AS movie_count
FROM casting c
JOIN movie m ON c.movieid = m.id
WHERE c.actorid = (SELECT id FROM actor WHERE name = 'John Travolta')
GROUP BY m.yr
ORDER BY count(*) DESC;
| yr | movie_count |
|---|---|
| 1976 | 2 |
| 1977 | 2 |
| 1978 | 2 |
| 1981 | 2 |
| 1994 | 2 |
| 1989 | 1 |
SELECT 6