none
Filter Top N Customers RRS feed

  • Question

  • Hi , I have a seemingly easy task , to filter top N Customers, 

    	;with Cte as
    	(select 'Mike' as Customer, 'Chair' as product, 100 as 'Price'
    	union all
    	select 'Mike' as Customer, 'Table' as product, 11 as 'Price'
    	union all
    	select 'Mike' as Customer, 'Bed' as product, 5 as 'Price'
    	union all
    	select 'Tom' as Customer, 'Bed' as product, 5 as 'Price'
    	union all
    	select 'Tom' as Customer, 'PC' as product, 500 as 'Price'
    	union all
    	select 'Tina' as Customer, 'PC' as product, 550 as 'Price'
    	union all
    	select 'Zena' as Customer, 'Lamp' as product, 3 as 'Price')
    
    	, cte1 as (select customer, product, price, sum(price) over (partition by Customer) as CustomerTotal from cte)
    
    ,	cte2 as (select customer, product, price, CustomerTotal
    	,  Rank()  OVER(PARTITION BY 'a' ORDER BY CustomerTotal DESC) AS 'TopNCustomerRank' 
    	,  row_number() OVER(PARTITION BY 'a' ORDER BY CustomerTotal DESC) AS 'TopNCustomerRowNum' 
    		from cte1)
     
    		select customer, product, price, CustomerTotal,TopNCustomerRank
    	,  Rank()  OVER(PARTITION BY 'a' ORDER BY TopNCustomerRank ) AS 'TopNCustomerRank' 
    	 
    		from cte2

    I am getting not sequential (1,2,3,4) ranking in TopNCustomerRank field

    I hoped to get sequential ranking (1,2,3,4) for each CustomerTotal in TopNCustomerRank field, without applying additional grouping and here is the desired output. How do I achieve it? With this output I will add WHERE clause to filter out TopNCustomerRank>=@N and filter   Top N customers.

    Thank you in advance!!!!


    • Edited by BrBa Friday, December 6, 2019 2:45 PM
    Friday, December 6, 2019 2:44 PM

Answers

  • Hi,

    Just apply 

    row_number()  OVER(PARTITION BY 'a' ORDER BY TopNCustomerRank ) AS 'TopNCustomerRank' 
    from cte2

    if not try below one

     dense_rank()  OVER(PARTITION BY 'a' ORDER BY TopNCustomerRank ) AS 'TopNCustomerRank' 

    from cte2


    Thanks

    Srinivasa Rao G, MCSE(Business Intelligence) Blog: TechTalksPro

    Please mark as answer if my post is helped to solve your problem
    and vote as helpful if it helped so that forum users can benefit

    • Marked as answer by BrBa Friday, December 6, 2019 2:57 PM
    Friday, December 6, 2019 2:54 PM

All replies

  • Hi,

    Just apply 

    row_number()  OVER(PARTITION BY 'a' ORDER BY TopNCustomerRank ) AS 'TopNCustomerRank' 
    from cte2

    if not try below one

     dense_rank()  OVER(PARTITION BY 'a' ORDER BY TopNCustomerRank ) AS 'TopNCustomerRank' 

    from cte2


    Thanks

    Srinivasa Rao G, MCSE(Business Intelligence) Blog: TechTalksPro

    Please mark as answer if my post is helped to solve your problem
    and vote as helpful if it helped so that forum users can benefit

    • Marked as answer by BrBa Friday, December 6, 2019 2:57 PM
    Friday, December 6, 2019 2:54 PM
  • OMG, DENSE_RANK, or course!!!!

    Thank you! This was VERY HELPFUL!

     

    Friday, December 6, 2019 2:57 PM
  • What the goal to use a literal in your partition by clause?
    Friday, December 6, 2019 3:34 PM
    Moderator