Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> 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
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
-----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
Yuvaraj IND 27 Yuvaraj IND 42 Yuvaraj IND 12 Lara WI 83 Sewag IND 47
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: 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 Sun Sep 09 2001 - 23:16:00 CDT