top 5 on the basis of a zone

[sonalshastry]

Hi,

I need some help in writing a SQL query

case : in need top 3client on the basis of the their bill amount (highest comes first) for all the branches

Expected output

Client id Bill amount Branch
11111 100000 A
22222 199999 A
33333 199998 A

44444 12315 B
55555 5444 B
66666 5410 B

[deepuv04]

Hi,
Assuing the table has columns
ClientId, BillAmount and Branch

Use the following query
SELECT CleintId,BillAm ount,Branch
FROM (SELECT CleintId,BillAm ount,Brank,
DENSE_RANK() OVER (PARTITION BY Branch ORDER BY BillAmount DESC) AS Rank
FROM Table_Name
) as T
WHERE T.Rank <= 3

[nbiswas]

Solution to top 5 on the basis of a zone

I have a bit dilemma in interpreting ur question.

I have interpreted it in 2 ways

Case 1: You need to have only the top 3 customers for the whole set of branches who pays the highest bill

declare @t table(Clientid int, Billamount int, Branch varchar(10))
insert into @t 
	select 11111,100000,'A' union all select 22222,199999, 'A' union all
	select 33333,199998,'A' union all select 44444,12315, 'B' union all
	select 55555,5444,'B' union all select 66666, 5410, 'B'

Query:

select Clientid,Billamount,Branch
from
(
select 
	Dense_Rank() over(order by Billamount desc) as rn
	,t.* 
from @t t
)X where X.rn <=3

Output:

Clientid Billamount Branch

22222	199999	A
33333	199998	A
11111	100000	A

Case 2: For every branch u need the top 3 customers who pays the highest bill

Query:

select Clientid,Billamount,Branch
from
(
select 
	Dense_Rank() over(partition by Branch order by Billamount desc) as rn
	,t.* 
from @t t) X
where X.rn <=3

Output:

Clientid Billamount Branch

22222	199999	A
33333	199998	A
11111	100000	A
44444	12315	B
55555	5444	B
66666	5410	B