RE: Sql query

From: Swapna_Chinnagangannagari <Swapna_Chinnagangannagari_at_satyam.com>
Date: Sun, 09 Sep 2001 22:09:23 -0700
Message-ID: <F001.00387BAF.20010909220518@fatcity.com>

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_at_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_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 Mon Sep 10 2001 - 00:09:23 CDT