t-SQL to find Top 10 Records for Each Group

Question

I am trying to figure out how to return the top 10 records for each group of Trans.TranSID.

SELECT a.ABID, a.ABName, t.TranSID, SUM(IIF(TranTypeID = 'CO', td.Qty * CAST(td.Price AS money) * - 1, 
                      td.Qty * CAST(td.Price AS money))) AS TotalSales
FROM         Trans t INNER JOIN
                      TransDetail td ON t.TranID = td.TranID INNER JOIN
                      ABook a ON t.TranABID = a.ABID
WHERE     (t.TranDate BETWEEN CONVERT(DATETIME, '2012-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2013-01-01 00:00:00', 102)) AND 
           t.TranTypeID in ('SO','CA','CO') AND (t.TranStatus <> 'V')
GROUP BY a.ABID, a.ABName, t.TranSID
HAVING  (NOT (a.ABName LIKE '%cash%'))
ORDER BY t.TranSID, TotalSales Desc

I can add "TOP 10" to the select statement, but that gives me the top 10 accounts regardless of the group. There are 25 groups of Trans.TranSID and I'm trying to get the top 10 only for each group.

Answer 1

I think you're looking for ROW_NUMBER() with a PARTITION BY

SELECT * 
FROM (
    SELECT 
        ROW_NUMBER() OVER(PARTITION BY t.TranSID ORDER BY t.TranSID, SUM(IIF(TranTypeID = 'CO', td.Qty * CAST(td.Price AS money) * - 1, td.Qty * CAST(td.Price AS money))) DESC) as RowNum,            
        a.ABID, 
        a.ABName, 
        t.TranSID, 
        SUM(IIF(TranTypeID = 'CO', td.Qty * CAST(td.Price AS money) * - 1, td.Qty * CAST(td.Price AS money))) AS TotalSales
    FROM Trans t 
       INNER JOIN TransDetail td 
           ON t.TranID = td.TranID 
       INNER JOIN ABook a 
           ON t.TranABID = a.ABID
    WHERE (t.TranDate BETWEEN CONVERT(DATETIME, '2012-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2013-01-01 00:00:00', 102)) 
       AND t.TranTypeID in ('SO','CA','CO')
       AND (t.TranStatus <> 'V')
    GROUP BY a.ABID, a.ABName, t.TranSID
    HAVING  (NOT (a.ABName LIKE '%cash%'))
) a
WHERE a.RowNum <=10

This will assign a row number to each record in the grouping (the column defined by the PARTITION, going from 1 to n. From there, you can run a SELECT on it to grab any number of records per group.