get top 5 max count for each month

Question

get top 5 max count for each month

michaelyarbrough1975

Say Hey Kid

Points: 683
More actions
August 1, 2020 at 11:19 pm

#3776605
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.
- This topic was modified 4 years, 4 months ago by michaelyarbrough1975.
Attachments:
You must be logged in to view attached files.

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

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

pietlinden SSC Guru Points: 63509 More actions · Answer 1

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 More actions · Answer 2

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: 1004406 More actions · Answer 3

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.

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

tinafmills SSC Enthusiast Points: 197 More actions · Answer 4

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: 63509 More actions · Answer 5

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 4 years, 4 months ago by pietlinden.

pietlinden SSC Guru Points: 63509 More actions · Answer 6

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

This reply was modified 4 years, 4 months ago by pietlinden. Reason: duplicate post

tinafmills SSC Enthusiast Points: 197 More actions · Answer 7

tinafmills

SSC Enthusiast

Points: 197

August 2, 2020 at 4:51 am

#3776641

It works! Thank you pietlinden! 🙂

pietlinden SSC Guru Points: 63509 More actions · Answer 8

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

sgmunson SSC Guru Points: 110640 More actions · Answer 9

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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

get top 5 max count for each month

Attachments: