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
7 FROM Player
8* WHERE Team = 'IND'
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
[EMAIL PROTECTED]
214.954.1781
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]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:[EMAIL PROTECTED]]
> > 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:[EMAIL PROTECTED]]
> > 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
> > [EMAIL PROTECTED]
> > 214.954.1781
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Larry Elkins
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
