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.

Including a basic table structure and expected output will help get you answers. — jTC, Apr 30 '13 at 15:40
Basically there is a customer account table (Abook) and transaction (Trans) and transaction details (TransDetail) tables. Very simple structure. Simply want to sum sales for each customer and group the top 10 results for TranSID (which is the location of where the transaction took place). — GJGerson, Apr 30 '13 at 15:42

valverij · Accepted Answer · 2013-04-30T16:08:41.903

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.

I get the following error message when running that code. Msg 1033, Level 15, State 1, Line 20 The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified. — GJGerson, Apr 30 '13 at 16:06
@GaryGerson, I've updated my script. Try removing the `ORDER BY` at the bottom of the subquery — valverij, Apr 30 '13 at 16:09
Yep, that got it. I actually worked it out at the same time you were. — GJGerson, Apr 30 '13 at 16:14

score 1 · Answer 2 · answered Apr 30 '13 at 15:55

I'm not super familiar with t-sql specifically and unfortunately I don't have access to a t-sql database to test that this accomplishes your goal.

That said, I think this is one way you could accomplish it using a subquery and the ROW_NUMBER function.

SELECT
    *
FROM (
    SELECT 
        a.ABID
        , a.ABName
        , t.TranSID
        , SUM(IFF(TranTypeID = 'CO'
            , td.Qty * CAST(td.Price AS MONEY) * -1
            , td.Qty * CAST(td.Price AS MONEY))) AS TotalSales
        , ROW_NUMBER() 
            OVER(PARTITION BY t.TranSID 
                ORDER BY SUM(IFF(TranTypeID = 'CO'
                    , td.Qty * CAST(td.Price AS MONEY) * -1
                    , td.Qty * CAST(td.Price AS MONEY))) DESC) AS row
    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%'))
) q

WHERE
    q.row <= 10

Same as above, I get the following error message when I run this. Msg 1033, Level 15, State 1, Line 20 The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified. — GJGerson, Apr 30 '13 at 16:06
I believe the issue with your first version was that the ROW_NUMBER() field had "AS TotalSales" inside the sum function. What you have there now works. — GJGerson, Apr 30 '13 at 16:16

t-SQL to find Top 10 Records for Each Group

2 Answers2

Linked