clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2335776 fiddles created (27471 in the last week).

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') ; INSERT INTO casting(actorid, movieid) VALUES (1, 1) , (1, 12) , (2, 1) , (2, 2) , (2, 3) , (2, 4) , (2, 5) , (2, 6) , (2, 7) , (2, 8) , (2, 9) , (2, 10) , (2, 11) , (3, 11) ;
3 rows affected
12 rows affected
14 rows affected
 hidden batch(es)


-- 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
 hidden batch(es)


-- 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
 hidden batch(es)


-- 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
 hidden batch(es)