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
[EMAIL PROTECTED]
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
