List top N rentals for each month I'm trying to work out the SQL to list the top 5 rentals for each month on MySQL or SQL Server.  The database is the Sakila Sample database and the table is rental. Here are the first several rows in my database client (Navicat Premium):



I'd like the take the month from the rental_date field.
Each film has a unique inventory_id.

So far I've got this:

SELECT 
  MONTH( rental_date ) AS month,
    inventory_id,
    COUNT ( inventory_id ) AS no_of_rentals
FROM
    rental 
GROUP BY
    MONTH ( rental_date ),
    inventory_id 
HAVING
    COUNT ( inventory_id ) &gt; 1 
ORDER BY
    MONTH ( rental_date ),
    inventory_id;


Unfortunately, that returns all films with more than one rental per month, which is not what I want. 



I'd like to see something more like:

month   inventory_id  no_of_rentals
_________________________________
April   367           5
April   98            4
(3 more rows)
May     144           5
May     32            5
(3 more rows)
etc...


Bonus points if you can get the film title!

Thanks!

Rob

UPDATE:

Thanks to Dominique Boucher for the answer.

Here's my finished query:

WITH cte AS (
    SELECT 
      MONTH(rental_date) rental_month_no,
      DATENAME(month, rental_date) rental_month,
      inventory_id,
      COUNT( inventory_id ) no_of_rental,
      ROW_NUMBER( ) OVER ( PARTITION BY MONTH(rental_date) ORDER BY COUNT( inventory_id ) DESC) Rank
    FROM
      rental 
    GROUP BY
      MONTH(rental_date),
      DATENAME(month, rental_date),
      inventory_id 
    HAVING COUNT( inventory_id ) &gt; 1
) 
SELECT
    rental_month,
    cte.inventory_id,
    title,
    no_of_rental,
    rank
FROM
  cte
  JOIN inventory ON cte.inventory_id = inventory.inventory_id
  JOIN film ON inventory.film_id = film.film_id
WHERE
  Rank &lt; 6
ORDER BY rental_month_no, no_of_rental DESC;


I added code to fetch the titles and limited results to films that were rented more than once in a month.

Here are the results in Navicat:
Use pretty much the same script in a CTE but add the row number (partition by month).
This will return all rows with a row number in front, reseting at 1 for every month. Then you can just issue your query with a "where rn &lt;=5" and that will make it.  

Here's an example just to give you a starting point:

create table #Demo (Rental_id int identity, rental_month int, inventoryId int, customer_id int)

insert into #Demo values (2,556,495),(2,236,495),(2,116,495),(2,556,495),(2,556,495),(2,116,495),(2,323,495),(2,56,495),(2,49,495),(2,49,495),
(2,116,495),(2,556,495),(2,453,495),(2,236,495),(2,34,495),(2,116,495),(2,556,495),(2,556,495),(2,24,495),(2,24,495)

insert into #Demo values (3,117,495),(3,236,495),(3,117,495),(3,236,495),(3,117,495),(3,116,495),(3,117,495),(3,236,495),(3,117,495),(3,236,495),
(3,116,495),(3,556,495),(3,453,495),(3,236,495),(3,34,495),(3,117,495),(3,556,495),(3,556,495),(3,117,495),(3,24,495),(3,34,495)

go


with cte as (
select rental_month, inventoryId, COUNT(inventoryId) "no_of_rental", ROW_NUMBER() over (PARTITION by rental_month order by count(inventoryId)) "RN"
from #Demo
group by rental_month, inventoryId
)
select * from cte where RN &lt;6;


drop table #Demo