Using RANK with Count in a set of results

Question

Using RANK with Count in a set of results

JimReeves71

Default port

Points: 1410
More actions
January 10, 2014 at 8:37 am

#396089

I have a query which produces the following recordset
AssetCountbusiness_unit
100555 Hodder Education Group
86726 Octopus
83533 Hachette Children's Books
45859 Orion Publ. Grp
26039 Headline
25041 Hodder & Stoughton
20125 Little, Brown Book Group
1285 Taylor & Francis
6 NULL
This does a count of the business unit (AssetCount) and sorts this in desceding order
I would like to add a rank function...so that the Business Unit with the highest count...gets ranked 1,
then the next highest 2 and so on
However I am getting this
AssetCountbusiness_unit Rank
100555 Hodder Education Group 1
86726 Octopus 1
83533 Hachette Children's Books 1
45859 Orion Publ. Grp 1
26039 Headline 1
25041 Hodder & Stoughton 1
20125 Little, Brown Book Group 1
1285 Taylor & Francis 1
6 NULL 1
The query I am using, is as follows:
select
COUNT(1) as AssetCount,
e.business_unit
,RANK() OVER
(PARTITION BY e.business_unit ORDER BY COUNT(1) ) AS Rank
FROM dbo.editorial e
inner join dbo.access_history ah on e.record_id = ah.record_id
where ah.access_type = 4
and ah.access_time <= '01/01/2013'
GROUP by e.business_unit
order BY COUNT(1) desc
What am I doing wrong?

Viewing 5 posts - 1 through 4 (of 4 total)

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

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 1

Please also provide the table DDL and some sample data along with the expected outcome. Please review the article in my signature and post the necessary items. Thanks

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

x SSC-Insane Points: 23660 More actions · Answer 2

"PARTITION BY e.business_unit " will reset that ranking functionality back to 1 for each change of business_unit, maybe try "PARTITION BY 1" instead?

JimReeves71 Default port Points: 1410 More actions · Answer 3

patrickmcginnis59 10839 (1/10/2014)
"PARTITION BY e.business_unit " will reset that ranking functionality back to 1 for each change of business_unit, maybe try "PARTITION BY 1" instead?

Hi Patrick

Thanks very much for that. It worked!

Ok my refined query as follows:

select

COUNT(1) as AssetCount,

e.business_unit

,RANK() OVER

(PARTITION BY 1 ORDER BY COUNT(1) desc ) AS Rank

FROM dbo.editorial e

inner join dbo.access_history ah on e.record_id = ah.record_id

where ah.access_type = 4

and ah.access_time <= '01/01/2013'

GROUP by e.business_unit

order BY COUNT(1) desc

x SSC-Insane Points: 23660 More actions · Answer 4

awesome!

According to the technet page http://technet.microsoft.com/en-us/library/ms176102.aspx it looks like you could actually eliminate the "partition by 1" part, so might be worth it to try fiddling with the rank functionality and see how it works, I know I probably need to!