get top 5 max count for each month

  • michaelyarbrough1975

    Say Hey Kid

    Points: 683

    How do you return a list of the companies,  sales, and month for the top 5 sales for each month without merging 12 queries with a union? The script to create the table and data is attached.

    Attachments:
    You must be logged in to view attached files.
  • pietlinden

    SSC Guru

    Points: 62848

    This looks like it works:

    SELECT DISTINCT t.Company,sales.[Month], sales.Sales
    FROM TestTable t
    CROSS APPLY (SELECT TOP 5 *
    FROM [TestTable] t2
    WHERE t.Company = t2.Company
    ORDER BY t2.Sales DESC) sales
    ORDER BY t.Company, sales.Sales DESC;
  • michaelyarbrough1975

    Say Hey Kid

    Points: 683

    I changed the order by and it returned 12 records for the 1st month. I'm expecting 5 records for each month. Thank you.

    SELECT DISTINCT t.Company,sales.[Month], sales.Sales
    FROM TestTable t
    CROSS APPLY (SELECT TOP 5 *
    FROM [TestTable] t2
    WHERE t.Company = t2.Company
    ORDER BY t2.Sales DESC) sales
    ORDER BY sales.[Month]
  • Jeff Moden

    SSC Guru

    Points: 996843

    michaelyarbrough1975 wrote:

    I changed the order by and it returned 12 records for the 1st month. I'm expecting 5 records for each month. Thank you.

    SELECT DISTINCT t.Company,sales.[Month], sales.Sales
    FROM TestTable t
    CROSS APPLY (SELECT TOP 5 *
    FROM [TestTable] t2
    WHERE t.Company = t2.Company
    ORDER BY t2.Sales DESC) sales
    ORDER BY sales.[Month]

    You wanted the list by company... why did you remove the company from the ORDER BY?  Did you try it with it in it?

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • tinafmills

    SSC Enthusiast

    Points: 147

    I changed the order by to check the results return the top 5 per month. When you add the company to the order by it doesn't return the top 5 max sales per month. Thank you.

  • pietlinden

    SSC Guru

    Points: 62848

    I think this works... <g> Standard pattern is to do the TOP N inside the CROSS APPLY, and join the Cross Apply to the outer query. (that's what the tt.Month = mos.m is doing.)

    SELECT Mo = mos.m
    , s.Company
    , s.Sales
    FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) mos(m)
    CROSS APPLY (SELECT TOP 5 tt.Company, tt.Sales
    FROM dbo.TestTable tt
    WHERE tt.[Month] = mos.m) s

    Results:

    Mo Company Sales
    1 Experian Information Solutions Inc. 2774
    1 JPMORGAN CHASE & CO. 1948
    1 WELLS FARGO & COMPANY 1696
    1 CAPITAL ONE FINANCIAL CORPORATION 1526
    1 SYNCHRONY FINANCIAL 916
    2 Experian Information Solutions Inc. 2950
    2 JPMORGAN CHASE & CO. 1682
    2 CAPITAL ONE FINANCIAL CORPORATION 1524
    2 WELLS FARGO & COMPANY 1505
    2 SYNCHRONY FINANCIAL 884
    3 Experian Information Solutions Inc. 3224
    3 JPMORGAN CHASE & CO. 1913
    3 WELLS FARGO & COMPANY 1845
    3 CAPITAL ONE FINANCIAL CORPORATION 1462
    3 SYNCHRONY FINANCIAL 915
    4 Experian Information Solutions Inc. 2789
    4 JPMORGAN CHASE & CO. 1800
    4 WELLS FARGO & COMPANY 1784
    4 CAPITAL ONE FINANCIAL CORPORATION 1342
    4 SYNCHRONY FINANCIAL 845
    5 Experian Information Solutions Inc. 2299
    5 JPMORGAN CHASE & CO. 1963
    5 WELLS FARGO & COMPANY 1924
    5 CAPITAL ONE FINANCIAL CORPORATION 1216
    5 SYNCHRONY FINANCIAL 751
    6 Experian Information Solutions Inc. 2230
    6 JPMORGAN CHASE & CO. 1871
    6 WELLS FARGO & COMPANY 1831
    6 CAPITAL ONE FINANCIAL CORPORATION 1331
    6 SYNCHRONY FINANCIAL 700
    7 Experian Information Solutions Inc. 2411
    7 JPMORGAN CHASE & CO. 1942
    7 WELLS FARGO & COMPANY 1840
    7 CAPITAL ONE FINANCIAL CORPORATION 1270
    7 SYNCHRONY FINANCIAL 814
    8 Experian Information Solutions Inc. 2385
    8 JPMORGAN CHASE & CO. 1941
    8 WELLS FARGO & COMPANY 1846
    8 CAPITAL ONE FINANCIAL CORPORATION 1410
    8 SYNCHRONY FINANCIAL 852
    9 Experian Information Solutions Inc. 2191
    9 JPMORGAN CHASE & CO. 1786
    9 WELLS FARGO & COMPANY 1767
    9 CAPITAL ONE FINANCIAL CORPORATION 1261
    9 SYNCHRONY FINANCIAL 761
    10 Experian Information Solutions Inc. 2174
    10 WELLS FARGO & COMPANY 1819
    10 JPMORGAN CHASE & CO. 1807
    10 CAPITAL ONE FINANCIAL CORPORATION 1331
    10 SYNCHRONY FINANCIAL 763
    11 Experian Information Solutions Inc. 2376
    11 JPMORGAN CHASE & CO. 1610
    11 WELLS FARGO & COMPANY 1526
    11 CAPITAL ONE FINANCIAL CORPORATION 1224
    11 SYNCHRONY FINANCIAL 839
    12 Experian Information Solutions Inc. 2227
    12 JPMORGAN CHASE & CO. 1799
    12 WELLS FARGO & COMPANY 1577
    12 CAPITAL ONE FINANCIAL CORPORATION 1362
    12 SYNCHRONY FINANCIAL 792

    • This reply was modified 1 week, 2 days ago by  pietlinden.
  • pietlinden

    SSC Guru

    Points: 62848

    (sorry, my internet is freaking out... double post).

    • This reply was modified 1 week, 2 days ago by  pietlinden. Reason: duplicate post
  • tinafmills

    SSC Enthusiast

    Points: 147

    It works! Thank you pietlinden! 🙂

  • pietlinden

    SSC Guru

    Points: 62848

    You're welcome... sorry it took me a bit to actually understand the question.

  • sgmunson

    SSC Guru

    Points: 110506

    Not sure about whether performance will matter here, but the following code might be better on performance:

    WITH ORDERED_DATA AS (

    SELECT
    T.Company,
    T.[Month],
    T.Sales,
    ROW_NUMBER() OVER(PARTITION BY T.[Month] ORDER BY T.Sales DESC) AS RN
    FROM dbo.TestTable AS T
    )
    SELECT
    OD.[Month],
    DATENAME(month, DATEFROMPARTS(1900, OD.[Month], 1)) AS [MonthName],
    OD.Company,
    OD.Sales
    FROM ORDERED_DATA AS OD
    WHERE OD.RN < 6
    ORDER BY
    OD.[Month],
    OD.RN;

    Steve?(aka sgmunson)?:) 🙂 :)?
    Health & Nutrition
    Make Guaranteed Income

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply