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).
