Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2012
    Posts
    18

    Unanswered: ranking urgent help

    Hey guys and girls

    I am new to the forum so please be gentle !

    i am trying to create a rank formula, however i am a little stuck

    i just need to return the highest MCC count for the mcc code and return the seller code

    So eg row 3 and 4, there are two MMC CODE called 4772, i need the formula to return the seller code r10, this is because r10 has 9 counts against that mcc code, and r03 has 9,


    Please find attached screen shot
    Attached Thumbnails Attached Thumbnails rank query.JPG  

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Hi,

    Try:

    Code:
    with
        CTE_Group as
        (
            select seller_code, mcc_code, count(mcc_code) as mcc_count
            from [FDMS].[dbo].[Dim_Outlet]
            where ( (Rm_Sales_Band = '2M to 4m') or (Rm_Sales_Band = '4m +') ) and
                    (Seller_Code like 'r%')
        ),
    
        CTE_RowNum as
        (
            select 
                c.*, 
                ROW_NUMBER() OVER(PARTITION BY mcc_code 
                                  ORDER BY mcc_count desc) as RowNum
            from CTE_Group
        ) 
    
    select * from CTE_RowNum
    where RowNum = 1
    Hope this helps.

  3. #3
    Join Date
    Aug 2012
    Posts
    18

    thank you

    That worked a treat thank you very much

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •