SQL group by highest occurance

Question

By executing this query

SELECT year, genre, COUNT(genre)
FROM Oscar
GROUP BY year, genre

I got the following output:

2016        Action      2           
2016        Romance     1           
2017        Action      1           
2017        Romance     2           
2018        Fantasy     1           
2019        Action      1           
2019        Fantasy     2           
2020        Action      3           
2020        Fantasy     1           
2020        Romance     1   

Now i want to display only the genre with the highest number per year to display. What is the best way to do this?

So I want the output to look like this:

2016 Action
2017 Romance
2018 Fantasy
2019 Fantasy
2020 Action

Answer 1

You can use window functions:

SELECT year, genre
FROM (
    SELECT year, genre, RANK() OVER(PARTITION BY year ORDER BY COUNT(*) DESC) rn
    FROM Oscar
    GROUP BY year, genre
) t
WHERE rn = 1

If your database does not support window functions (eg MySQL < 8.0), another option is:

SELECT year, genre
FROM Oscar o
GROUP BY year, genre
HAVING COUNT(*) = (
    SELECT COUNT(*) 
    FROM Oscar o1 
    WHERE o1.year = o.year 
    GROUP BY o1.category 
    ORDER BY COUNT(*) DESC LIMIT 1
)