Select TOP 3 of every month

Question

I need to return the top 3 of a query each month:

select distinct  mes, rubrica, count(*) qtd
from boletins
group by mes, rubrica
order by mes asc, qtd desc;

This query above returns about 30 lines for each month, but only need the first 3 as below:

Answer 1

Here is a suggestion to test using the Row_number function to number the lines per month and in descending order of quantity:

with CTE_RN as
(
    select 
        mes, 
        rubrica, 
        count(*) qtd,
        row_number() over(partition by mes order by count(*) desc) as RN
    from boletins 
    group by 
        mes, 
        rubrica 
)

select mes, rubrica, qtd
from CTE_RN
where RN <= 3
order by mes asc, qtd desc

I hope it helps