| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Sql query
Nirmal,
And what about "ties" and the double (or triple or more) summation that would occur?
The reason for *not* using DENSE_RANK or RANK for this query is the way they handle ties -- assigning the *same* rank to values with the same value. This nature would make it difficult, for example, if you had 4 top scores with the same value. Or, what if someone had scores of 80,80,70,70,60,60? With RANK I would get 1,1,3,3,5,5 and with DENSE_RANK I would get 1,1,2,2,3,3. While his example did not include ties, I used that approach anyway since a "real" example might contain ties.
Here is an example with ties and how ROW_NUMBER, RANK, and DENSE_RANK behave:
1 SELECT Pname,
2 Team, 3 Score, 4 ROW_NUMBER () OVER (PARTITION BY Pname ORDER BY Score DESC) RowNumber, 5 RANK () OVER (PARTITION BY Pname ORDER BY Score DESC) RANK, 6 DENSE_RANK () OVER (PARTITION BY Pname ORDER BY Score DESC)DenseRank
Row Dense
PNAME TEAM SCORE Number RANK Rank
---------- ----- ----- ------- ---- -----
Dravid IND 53 1 1 1
Dravid IND 53 2 1 1
Dravid IND 32 3 3 2
Dravid IND 32 4 3 2
Sewag IND 47 1 1 1
Tendulkar IND 138 1 1 1
Tendulkar IND 138 2 1 1
Tendulkar IND 138 3 1 1
Tendulkar IND 138 4 1 1
Tendulkar IND 83 5 5 2
Tendulkar IND 67 6 6 3
Tendulkar IND 42 7 7 4
Yuvaraj IND 42 1 1 1
Yuvaraj IND 27 2 2 2
Yuvaraj IND 12 3 3 3
If I had used DENSE_RANK with the "SUM(DECODE", Tendulkar's "highest" score would have caused 138 to be summed 4 times into score1. For Dravid, we would have doubled the values of 53 and 32. And with Dravid, DENSE_RANK would have placed the values in score1 and score2. With RANK, this would have placed the doubled values in score1 and score3 without a score2. With ROW_NUMBER, this problem is avoided. So, I use RANK and DENSE_RANK when simply outputting data but when I want the top 3, least 3, etc, I use the ROW_NUMBER function since the "same" values will not be assigned the same rank.
It also gets into the semantics of what one means by top3 -- the top 3 unique scores for a person or simply the "first" 3. Regardless, I still wouldn't want to double or triple sum a value.
Regards,
Larry G. Elkins
elkinsl_at_flash.net
214.954.1781
> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Nirmal
> Kumar Muthu Kumaran
> Sent: Monday, September 10, 2001 7:00 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Sql query
>
>
> The query becomes perfect, if you replace ROW_NUMBER() BY DENSE_RANK().
>
> --nIRMAL.
>
> > -----Original Message-----
> > From: Swapna_Chinnagangannagari
> > [SMTP:Swapna_Chinnagangannagari_at_satyam.com]
> > Sent: Monday, September 10, 2001 9:05 AM
> > To: Multiple recipients of list ORACLE-L
> > Subject: RE: Sql query
> >
> > Hello Larry,
> >
> > Thanks alot for u'r immediate response
> > but i'm a not old bee in sql queries
> > can u please elaborate on the line
> >
> > ROW_NUMBER () OVER (PARTITION BY Pname ORDER BY Score
> >
> > Regards
> > Swapna
> >
> > -----Original Message-----
> > From: Larry Elkins [SMTP:elkinsl_at_flash.net]
> > Sent: Monday, September 10, 2001 10:55 AM
> > To: Multiple recipients of list ORACLE-L
> > Subject: RE: Sql query
> >
> > The following works with 8.1.6 and above:
> >
> > 1 SELECT T3.Pname,
> > 2 T3.Team,
> > 3 Sum(Decode(T3.Top3,1,T3.Score)) Score1,
> > 4 Sum(Decode(T3.Top3,2,T3.Score)) Score2,
> > 5 Sum(Decode(T3.Top3,3,T3.Score)) Score3
> > 6 FROM (SELECT Pname,
> > 7 Team,
> > 8 Score,
> > 9 ROW_NUMBER () OVER (PARTITION BY Pname ORDER BY Score
> > DESC) Top3
> > 10 FROM Player
> > 11 WHERE Team = 'IND') T3
> > 12 WHERE T3.Top3 <= 3
> > 13 GROUP BY T3.PName,
> > 14 T3.Team
> > 15* ORDER BY nvl(Score1,0)+nvl(Score2,0)+nvl(Score3,0) DESC
> > SQL> /
> >
> > PNAME TEAM SCORE1 SCORE2 SCORE3
> > -------------------- ---------- ---------- ---------- ----------
> > Tendulkar IND 138 83 67
> > Dravid IND 53 32
> > Yuvaraj IND 42 27 12
> > Sewag IND 47
> >
> > I wasn't sure of the order was important, but, your output (maybe by
> > chance)
> > was in descending order of the sum of the top 3 grades, thus
> the order by
> > clause you see above. Ditch it if it should be something else.
> >
> > Regards,
> >
> > Larry G. Elkins
> > elkinsl_at_flash.net
> > 214.954.1781
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Larry Elkins INET: elkinsl_at_flash.net Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Mon Sep 10 2001 - 10:01:02 CDT
 |
 |