Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE tbl (title text, genres text[], imdb_score numeric);
INSERT INTO tbl VALUES
  ('Five Came Back: The Reference Films', '{documentation}' , NULL)
, ('Taxi Driver'                        , '{crime,drama}'   , 8.3 )
, ('Monty Python and the Holy Grail'    , '{comedy,fantasy}', 8.2 )
, ('The Life of Brian'                  , '{comedy,fantasy}', 8.2 )  -- to show duplicate winners
, ('Borat'                              , '{comedy}'        , 0.2 )  -- to add a loser
, ('Life of Brian'                      , '{comedy}'        , 8   )
, ('The Exorcist'                       , '{horror}'        , 8.1 )
;
7 rows affected

SELECT DISTINCT ON (genre) *
FROM (
   SELECT unnest(genres) AS genre, title, imdb_score
   FROM   tbl
   ) sub
ORDER  BY genre, imdb_score DESC NULLS LAST;


genre
title
imdb_score


comedy
The Life of Brian
8.2

crime
Taxi Driver
8.3

documentation
Five Came Back: The Reference Films
null

drama
Taxi Driver
8.3

fantasy
The Life of Brian
8.2

horror
The Exorcist
8.1


-- if you insist on max() ...
-- also returning *all* winning films per genre
WITH flat AS (
   SELECT unnest(genres) AS genre, imdb_score, title
   FROM   tbl
   )
SELECT genre, title, imdb_score
FROM  (
   SELECT genre, max(imdb_score) AS imdb_score
   FROM   flat
   GROUP  BY 1
   ) sub2
JOIN   flat f USING (genre, imdb_score)
ORDER  BY genre, title;


genre
title
imdb_score


comedy
Monty Python and the Holy Grail
8.2

comedy
The Life of Brian
8.2

crime
Taxi Driver
8.3

drama
Taxi Driver
8.3

fantasy
Monty Python and the Holy Grail
8.2

fantasy
The Life of Brian
8.2

horror
The Exorcist
8.1


-- but then, rank() is still simpler & faster
-- and does not eliminate NULL scores (if there is no other)
SELECT genre, title, imdb_score
FROM  (
   SELECT genre, title, imdb_score
        , rank() OVER (PARTITION BY genre ORDER BY imdb_score DESC NULLS LAST) AS rnk
   FROM  (
      SELECT unnest(genres) AS genre, imdb_score, title
      FROM   tbl
      ) sub
   ) sub2
WHERE  rnk = 1
ORDER  BY genre, title;


genre
title
imdb_score


comedy
Monty Python and the Holy Grail
8.2

comedy
The Life of Brian
8.2

crime
Taxi Driver
8.3

documentation
Five Came Back: The Reference Films
null

drama
Taxi Driver
8.3

fantasy
Monty Python and the Holy Grail
8.2

fantasy
The Life of Brian
8.2

horror
The Exorcist
8.1

genre	title	imdb_score
comedy	The Life of Brian	8.2
crime	Taxi Driver	8.3
documentation	Five Came Back: The Reference Films	null
drama	Taxi Driver	8.3
fantasy	The Life of Brian	8.2
horror	The Exorcist	8.1

genre	title	imdb_score
comedy	Monty Python and the Holy Grail	8.2
comedy	The Life of Brian	8.2
crime	Taxi Driver	8.3
drama	Taxi Driver	8.3
fantasy	Monty Python and the Holy Grail	8.2
fantasy	The Life of Brian	8.2
horror	The Exorcist	8.1