RE: Sql query

From: Larry Elkins <elkinsl_at_flash.net>
Date: Sun, 09 Sep 2001 22:28:07 -0700
Message-ID: <F001.00387BCD.20010909223517@fatcity.com>

Swapna,

ROW_NUMBER is one of the analytical functions introduced in 8.1.6. You can find more detailed info on it in the SQL Manual and in the Data Warehousing Guide. Execute just the in-line view and you can see how it works:

1 SELECT Pname,

  2         Team,
  3         Score,
  4         ROW_NUMBER () OVER (PARTITION BY Pname ORDER BY Score DESC) Top3

5 FROM Player
6* WHERE Team = 'IND'
SQL> /

PNAME                TEAM            SCORE       TOP3
-------------------- ---------- ---------- ----------
Dravid               IND                53          1
Dravid               IND                32          2
Sewag                IND                47          1
Tendulkar            IND               138          1
Tendulkar            IND                83          2
Tendulkar            IND                67          3
Tendulkar            IND                42          4
Yuvaraj              IND                42          1
Yuvaraj              IND                27          2
Yuvaraj              IND                12          3

10 rows selected.

ROW_NUMBER is simply an integer value starting at 1 and incrementing by 1 assigned to rows from a query, but not quite like ROWNUM. Notice the ORDER BY Score DESC on line 4. This will order the scores descending. Because I said PARTITION BY Pname, this will reset the ROW_NUMBER value for each PNAME. So, it is ordering the scores desc *within* each Pname, starting over at 1 for each Pname. The output above explains it better.

Regards,

Larry G. Elkins
elkinsl_at_flash.net
214.954.1781

-----Original Message-----
Swapna_Chinnagangannagari
Sent: Monday, September 10, 2001 1:05 AM To: Multiple recipients of list ORACLE-L

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

--

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:28:07 CDT