What would be the best way to filter grouped table by its max values?

Question

I have this following table, which tells me how many rentals a certain film had in a certain month. Here's the top 10 rows:

| month | title             | rentals |
+-------+-------------------+---------+
|     2 | ACE GOLDFINGER    |       1 |
|     2 | AFFAIR PREJUDICE  |       1 |
|     2 | AFRICAN EGG       |       1 |
|     2 | ALI FOREVER       |       1 |
|     2 | ALONE TRIP        |       1 |
|     2 | AMADEUS HOLY      |       1 |
|     2 | AMERICAN CIRCUS   |       1 |
|     2 | AMISTAD MIDSUMMER |       1 |
|     2 | ARMAGEDDON LOST   |       1 |
|     2 | BAKED CLEOPATRA   |       1 |
+-------+-------------------+---------+

My main objective here is to create a new table where, for each month, it gives me the title of the filme with the most rentals in that month.

So far, I've tried using a combination of group by queries, but it didn't gave much result. Despite that, I achieved to create a new table that gives me the number of rentals the top movie (or movies) had in each month. Here it is:

CREATE VIEW temp AS (SELECT month, MAX(rentals) rentals FROM film_per_month GROUP BY 1);

mysql> SELECT * FROM temp;
+-------+---------+
| month | rentals |
+-------+---------+
|     2 |       2 |
|     5 |       5 |
|     6 |       7 |
|     7 |      16 |
|     8 |      13 |
+-------+---------+
5 rows in set (0.05 sec)

The obstacle here is that I can't extract it to show the titles of the movies that were rented that maximum amount of times.

I've tried to amend that using inner join, self-joins, but I just messed it up.

So my question is: What would be the better way to create a new table where, for each month, it gives me the title of the filme with the most rentals in that month?

Answer 1

I think you can try to use EXISTS subquery to get Max rentals for each month rows.

SELECT t1.*
FROM film_per_month t1
WHERE EXISTS (
   SELECT 1
    FROM film_per_month  tt
    WHERE tt.month = t1.month
    GROUP BY tt.month
    HAVING MAX(tt.rentals)  = t1.rentals
)