From Swapna_Chinnagangannagari@satyam.com Sun, 09 Sep 2001 22:09:23 -0700 From: Swapna_Chinnagangannagari Date: Sun, 09 Sep 2001 22:09:23 -0700 Subject: RE: Sql query Message-ID: MIME-Version: 1.0 Content-Type: text/plain Title: 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:elkinsl@flash.net] 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 elkinsl@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@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@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).