Home » SQL & PL/SQL » SQL & PL/SQL » Need help in SQL Join and Ranking
Need help in SQL Join and Ranking [message #233213] Wed, 25 April 2007 04:01 Go to next message
dumdum
Messages: 25
Registered: April 2007
Junior Member
Suppose there are 2 tables X & Y
Table X has attributes ID & NAME
Table Y has attributes ID & ACCT#
One ID can have more than 1 ACCT#

How to list the top 3 ID having the largest no. of ACCT# with no. of ACCT# & name ordered by no. of ACCT#?

If all the ID are listed, is this correct?
SELECT ID,NAME,COUNT(DISTINCT ACCT#)
FROM X,Y
WHERE X.ID = Y.ID
ORDERED BY COUNT(DISTINCT ACCT#)

Thx!

Re: Need help in SQL Join and Ranking [message #233220 is a reply to message #233213] Wed, 25 April 2007 04:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
with data as (
  select x.id, x.name, count(y.acct#) cnt,
         row_number() over (order by count(y.acct#) desc) rn
  from x, y 
  where y.id=x.id
  group by x.id, x.name
  )
select x.id, x.name, x.cnt
from data
where rn <= 3;

Regards
Michel
Re: Need help in SQL Join and Ranking [message #233223 is a reply to message #233220] Wed, 25 April 2007 04:18 Go to previous message
dumdum
Messages: 25
Registered: April 2007
Junior Member
You are marvelous.Thank you so much!
Previous Topic: about view
Next Topic: plz help me in getting the query
Goto Forum:
  


Current Time: Fri Dec 09 11:31:01 CST 2016

Total time taken to generate the page: 0.04754 seconds