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
> -----Original Message-----
> Swapna_Chinnagangannagari
> Sent: Sunday, September 09, 2001 9:45 PM
> To: Multiple recipients of list ORACLE-L
>
> Hello Friends I am struck up with typical problem.
> I got this problem while querying data from Oracle Tables.
> I can't explain the problem as it is with my project business jargons so
> I am formulated the problem in following way.
> Let us assume that table and data of it as given below:
> TABLE : PLAYER
> PLAYER NAME TEAM SCORE
> Tendulkar IND 83
> Tendulkar IND 42
> Tendulkar IND 138
> Tendulkar IND 67
> Tendulkar BOMBAY 159
> Dravid IND 32
> Dravid IND 53
> Dravid SZONE 72
> Yuvaraj NZONE 91
> Yuvaraj IND 27
> Yuvaraj IND 42
> Yuvaraj IND 12
> Lara WI 83
> Sewag IND 47
> Sewag NZONE 17
> I want the report based on the above table data as follows:
> I want player name and his best 3 scores played for the team IND.
> Report has to be look like as given below.
> To get the following report output I need One-shot-SQL query? (I don't
> want
> any PL/SQL as solution)
> PLAYER TEAM SCORE1 SCORE2 SCORE3
> Tendulkar IND 138 83 67
> Dravid IND 53 32
> Yuvaraj IND 42 27 12
> Sewag IND 47
>
> --
> 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).
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Nirmal Kumar Muthu Kumaran
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).
