SQL order with equal group size

I have a table with columns month, name and transaction_id. I would like to count the number of transactions per month and name. However, for each month I want to have the top N names with the highest transaction counts.

The following query groups by month and name. However the LIMIT is applied to the complete result and not per month:

SELECT
    month,
    name,
    COUNT(*) AS transaction_count
FROM my_table
GROUP BY month, name
ORDER BY month, transaction_count DESC
LIMIT N

​x
 
SELECT    month,    name,    COUNT(*) AS transaction_countFROM my_tableGROUP BY month, nameORDER BY month, transaction_count DESCLIMIT N​

Does anyone have an idea how I can get the top N results per month?

Answer

Use row_number():

SELECT month, name, transaction_count
FROM (SELECT month, name, COUNT(*) AS transaction_count,
             ROW_NUMBER() OVER (PARTITION BY month ORDER BY COUNT(*) DESC) as seqnum
      FROM my_table
      GROUP BY month, name
     ) mn
WHERE seqnum <= N
ORDER BY month, transaction_count DESC

 
SELECT month, name, transaction_countFROM (SELECT month, name, COUNT(*) AS transaction_count,             ROW_NUMBER() OVER (PARTITION BY month ORDER BY COUNT(*) DESC) as seqnum      FROM my_table      GROUP BY month, name     ) mnWHERE seqnum <= NORDER BY month, transaction_count DESC​

Advertisement

Answer