RE: Sql query

From: Larry Elkins <elkinsl_at_flash.net>
Date: Sun, 09 Sep 2001 21:16:00 -0700
Message-ID: <F001.00387B8A.20010909212517@fatcity.com>

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
-----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: 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