Post reply

Using RANK with Count in a set of results

  • January 10, 2014 at 8:37 am

    #396089

    I have a query which produces the following recordset

    AssetCount business_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

    AssetCount business_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?

  • January 10, 2014 at 8:39 am

    #1679206

    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]

  • January 10, 2014 at 8:43 am

    #1679208

    "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?

  • January 10, 2014 at 8:46 am

    #1679209

    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

  • January 10, 2014 at 8:59 am

    #1679218

    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!

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

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