# Filter Top N Customers

• ### 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.

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

• 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

and vote as helpful if it helped so that forum users can benefit

• Marked as answer by 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