By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,766 Members | 1,281 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,766 IT Pros & Developers. It's quick & easy.

top 5 on the basis of a zone

P: 6
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
Nov 30 '09 #1
Share this Question
Share on Google+
2 Replies


deepuv04
Expert 100+
P: 227
Hi,
Assuing the table has columns
ClientId, BillAmount and Branch

Use the following query
SELECT CleintId,BillAmount,Branch
FROM (SELECT CleintId,BillAmount,Brank,
DENSE_RANK() OVER (PARTITION BY Branch ORDER BY BillAmount DESC) AS Rank
FROM Table_Name
) as T
WHERE T.Rank <= 3
Dec 1 '09 #2

nbiswas
100+
P: 149
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

Expand|Select|Wrap|Line Numbers
  1. declare @t table(Clientid int, Billamount int, Branch varchar(10))
  2. insert into @t 
  3.     select 11111,100000,'A' union all select 22222,199999, 'A' union all
  4.     select 33333,199998,'A' union all select 44444,12315, 'B' union all
  5.     select 55555,5444,'B' union all select 66666, 5410, 'B'
Query:
Expand|Select|Wrap|Line Numbers
  1. select Clientid,Billamount,Branch
  2. from
  3. (
  4. select 
  5.     Dense_Rank() over(order by Billamount desc) as rn
  6.     ,t.* 
  7. from @t t
  8. )X where X.rn <=3
Output:

Clientid Billamount Branch
Expand|Select|Wrap|Line Numbers
  1. 22222    199999    A
  2. 33333    199998    A
  3. 11111    100000    A

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

Query:

Expand|Select|Wrap|Line Numbers
  1. select Clientid,Billamount,Branch
  2. from
  3. (
  4. select 
  5.     Dense_Rank() over(partition by Branch order by Billamount desc) as rn
  6.     ,t.* 
  7. from @t t) X
  8. where X.rn <=3
Output:

Clientid Billamount Branch
Expand|Select|Wrap|Line Numbers
  1. 22222    199999    A
  2. 33333    199998    A
  3. 11111    100000    A
  4. 44444    12315    B
  5. 55555    5444    B
  6. 66666    5410    B
Dec 2 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.