TOP 10 Names Per Month By Count - Row_Number/Partition OR Pivot? 1

[kjv1611]

I think I'm starting to get the hang of using RANK or ROW_NUMBER with partition in SQL. However, I've got one now that seems like it should be simple - Excel makes it look terribly simple with a Pivot Table - but I can't seem to match the results so far in SQL.

I'm hoping I'm just missing something simple.

I want to get the top 10 Names per month, ordered by the count of records associated with the names.

The Names have a one to many relationship... as in many records in this table can be linked to each Name.

For the months, I have a calculated field in the same query that breaks these into groups by month.
----------------
Example Data from table:

ID MyDate Name OtherFields
111 01/01/11 Leroy MiscUnimportantInfo
112 02/07/11 Leroy MiscUnimportantInfo
113 02/10/11 Leroy MiscUnimportantInfo
114 04/07/11 Leroy MiscUnimportantInfo
125 08/09/11 Leroy MiscUnimportantInfo
135 10/03/11 Leroy MiscUnimportantInfo
211 01/05/11 Jethro MiscUnimportantInfo
212 02/17/11 Jethro MiscUnimportantInfo
213 02/12/11 Jethro MiscUnimportantInfo
214 04/17/11 Jethro MiscUnimportantInfo
225 08/19/11 Jethro MiscUnimportantInfo
235 10/05/11 Jethro MiscUnimportantInfo
711 01/11/11 Billy Bob MiscUnimportantInfo
712 02/12/11 Billy Bob MiscUnimportantInfo
713 02/15/11 Billy Bob MiscUnimportantInfo
714 04/07/11 Billy Bob MiscUnimportantInfo
725 08/21/11 Billy Bob MiscUnimportantInfo
735 10/30/11 Billy Bob MiscUnimportantInfo
............

From this, I want the top 10 by count of each name per month... something like this:

MyMonth Name
Jan '11 Jethro
Jan '11 DonaldDuck
...
Feb '11 TheMouse
Feb '11 BillyBob
..
Mar '11 Jethro
Mar '11 BillyBob
..
Apr '11 SomebodySpecial
Apr '11 SomebodyNotSoSpecial
..

----------------
Sorry for the wordy explanation of my situation. Hopefully the example gives enough detail to make sense.

So this is what I've been trying:

SELECT	x.MyMonth,x.Name
FROM	(
	SELECT	LEFT(DATENAME(month,t.MyDate),3) + ' ''' + RIGHT(YEAR(t.MyDate),2) AS MyMonth ,t.Name
			,ROW_NUMBER() OVER(PARTITION BY LEFT(DATENAME(month,t.MyDate),3) + ' ''' + RIGHT(YEAR(t.MyDate),2) 
			ORDER BY t.Name DESC) AS RowNum
	FROM	MyTable t
	) x
WHERE x.RowNum < 11

The above query is not giving me what I need. I get duplicates on the Name field, where I really should just have one of each per month .... so the "top 10 names for each month" is giving me say 4 or 5 names, and one of the names 5 to 6 times.

Thanks for any advice, examples, references, etc.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[drlex]

I want the top 10 by count of each name per month"

In which case, wouldn't you need to be grouping by name and date, then doing the ordering on a count?

soi là, soi carré

 

[kjv1611]

Hmm.... You know what... I guess so.

Let me go back try that and post back. Thanks for pointing me in the right direction. I guess I was looking for some way to do it totally within ROW_NUMBER and PARTITION..

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

So, I guess I'm part of the way, but just realized not totally... right now, I'm just ordering by the Name, but I need to order by the count...

Here's what I have so far:

SELECT	MyMonth ,Name
FROM	(
	SELECT	MyMonth ,Name
			,ROW_NUMBER() OVER(PARTITION BY MyMonth ORDER BY so.Name DESC) AS RowNum
	FROM	(
		SELECT	LEFT(DATENAME(month,MyDate),3) + ' ''' + RIGHT(YEAR(MyDate),2) AS MyMonth ,Name ,COUNT(1) AS Records
		FROM	MyTable
		GROUP BY LEFT(DATENAME(month,MyDate),3) + ' ''' + RIGHT(YEAR(MyDate),2) ,Name
		) so
	) x
WHERE x.RowNum < 11

So now to sort out my sorts and all, so I'm sorting it correctly... I think I'm just being thick-headed, b/c I can't still get it there yet... at least I don't have duplicates any longer.


"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[drlex]

Order on Partition?

SELECT    MyMonth ,Name
FROM    (
    SELECT    MyMonth ,Name
            ,ROW_NUMBER() OVER(PARTITION BY MyMonth ORDER BY so.[red]Records[/red] DESC) AS RowNum
    FROM    (
        SELECT    LEFT(DATENAME(month,MyDate),3) + ' ''' + RIGHT(YEAR(MyDate),2) AS MyMonth ,Name ,COUNT(1) AS Records
        FROM    MyTable
        GROUP BY LEFT(DATENAME(month,MyDate),3) + ' ''' + RIGHT(YEAR(MyDate),2) ,Name
        ) so
    ) x
WHERE x.RowNum < 11

soi là, soi carré

 

[kjv1611]

Okay... .. I figured it was something goofy on my part. I'll blame it on being Monday morning, yeah that's it!

I just needed to sort on the Record column, or the counted column, not the Name

So I changed it to this and it works... and I'll toss the counted column into the results to prove it (well that's what I did on my end):

SELECT    MyMonth ,Name ,Records
FROM    (
    SELECT    MyMonth ,Name ,Records
            ,ROW_NUMBER() OVER(PARTITION BY MyMonth ORDER BY so.Records DESC) AS RowNum
    FROM    (
        SELECT    LEFT(DATENAME(month,MyDate),3) + ' ''' + RIGHT(YEAR(MyDate),2) AS MyMonth ,Name ,COUNT(1) AS Records
        FROM    MyTable
        GROUP BY LEFT(DATENAME(month,MyDate),3) + ' ''' + RIGHT(YEAR(MyDate),2) ,Name
        ) so
    ) x
WHERE x.RowNum < 11

Thanks again for pointing me in the right direction...

And I was just about to post this when I noticed your last reply on the Order By... yeah it was a duh moment. Thanks again, drlex.


"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[drlex]

You're welcome, kjv1611 ; thanks for the accolade. Sometimes it's just a fresh pair of eyes on the problem to spot the small niggle.

soi là, soi carré