RE: ROWNUM is driving me nuts - queries suggested produced no res
MaryAnn, the best way to understand rownum is to do the following: SQL SELECT ROWNUM, GENDER 2 FROM (SELECT ROWNUM, GENDER 3 FROM EMP2 4 WHERE ROWNUM = 20) You will quickly see that, no matter how you order the result set, the first record returned is rownum #1, second is rownum #2 etc. The rownum value is assigned as rows are RETURNED or DISPLAYED, not as they are selected. when you run your original query: SQL SELECT ROWNUM, GENDER 2 FROM (SELECT ROWNUM, GENDER 3 FROM EMP2 4 WHERE ROWNUM = 20) 5 WHERE ROWNUM 10; you should *not* get any rows because the first row returned has a value of 1 - hece you get nothing. the next row returned gets the value of 1 - again, nothing gets shown. just what is it you want to return in your query? Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, July 09, 2003 12:19 PM To: Multiple recipients of list ORACLE-L results I definitely dont fully understand ROWNUM yet, and you guys so far provided more info than a couple of books by Oracle, that I have here. For a second I thought I'm beginning to get it, but the queries suggested produced no results... SQL SELECT ROWNUM, GENDER 2 FROM (SELECT ROWNUM, GENDER 3 FROM EMP2 4 WHERE ROWNUM = 20) 5 WHERE ROWNUM 10; no rows selected SQL SELECT r, GENDER 2 FROM (SELECT ROWNUM r, GENDER 3 FROM EMP2 4 WHERE ROWNUM = 20) 5 WHERE ROWNUM 10; no rows selected SQL SELECT r ROWNUM, GENDER 2 FROM (SELECT ROWNUM r, GENDER 3 FROM EMP2 4 WHERE ROWNUM = 20) 5 WHERE ROWNUM 10; no rows selected SQL ... so, any help is appreciated - rownum is driving me nuts... thx maa __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: MaryAnn Atkinson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: ROWNUM is driving me nuts - queries suggested produced no res
Only less and = queries with regards to rownum are supported. RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 7/9/2003 11:19 AM I definitely dont fully understand ROWNUM yet, and you guys so far provided more info than a couple of books by Oracle, that I have here. For a second I thought I'm beginning to get it, but the queries suggested produced no results... SQL SELECT ROWNUM, GENDER 2 FROM (SELECT ROWNUM, GENDER 3 FROM EMP2 4 WHERE ROWNUM = 20) 5 WHERE ROWNUM 10; no rows selected SQL SELECT r, GENDER 2 FROM (SELECT ROWNUM r, GENDER 3 FROM EMP2 4 WHERE ROWNUM = 20) 5 WHERE ROWNUM 10; no rows selected SQL SELECT r ROWNUM, GENDER 2 FROM (SELECT ROWNUM r, GENDER 3 FROM EMP2 4 WHERE ROWNUM = 20) 5 WHERE ROWNUM 10; no rows selected SQL .. so, any help is appreciated - rownum is driving me nuts... thx maa __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: MaryAnn Atkinson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: ROWNUM is driving me nuts - queries suggested produced no res
To add on to my previous comments, change your code a bit: SQL SELECT RN, GENDER 2 FROM (SELECT ROWNUM RN, GENDER 3 FROM EMP2 4 WHERE ROWNUM = 20) 5 WHERE RN 10; -Original Message- To: Multiple recipients of list ORACLE-L Sent: 7/9/2003 11:19 AM I definitely dont fully understand ROWNUM yet, and you guys so far provided more info than a couple of books by Oracle, that I have here. For a second I thought I'm beginning to get it, but the queries suggested produced no results... SQL SELECT ROWNUM, GENDER 2 FROM (SELECT ROWNUM, GENDER 3 FROM EMP2 4 WHERE ROWNUM = 20) 5 WHERE ROWNUM 10; no rows selected SQL SELECT r, GENDER 2 FROM (SELECT ROWNUM r, GENDER 3 FROM EMP2 4 WHERE ROWNUM = 20) 5 WHERE ROWNUM 10; no rows selected SQL SELECT r ROWNUM, GENDER 2 FROM (SELECT ROWNUM r, GENDER 3 FROM EMP2 4 WHERE ROWNUM = 20) 5 WHERE ROWNUM 10; no rows selected SQL .. so, any help is appreciated - rownum is driving me nuts... thx maa __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: MaryAnn Atkinson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Re: ROWNUM is driving me nuts - queries suggested produced no results
Each row that is retrieved from the database that matches the WHERE condition is placed in a result set. The sequence within the result set is the value in the pseudocolumn ROWNUM. If Oracle is using a full-table scan, the first row of the first block is the first in the result set and is assigned ROWNUM 1. If Oracle uses an index to access the data, the first entry in the index is assigned ROWNUM 1. Once a complete result set is built, the ORDER BY clause is applied. The result may be that the order of the result set and the order presented to the user are not the same. If ROWNUM is used, the numbers may be and, in fact, usually are, out of sequence. ROWNUM and PREDICATES (the WHERE clause) ROWNUM can be used to restrict the amount of data returned by a query, as we will see shortly. ROWNUM is assigned for each row that matches all of the applicable conditions in the predicate. For example, ROWNUM is assigned to a row only if it matches the deptno condition. One common mistake is trying to use ROWNUM to find values greater than 1. ROWNUM conditions can only be equal to 1 or less than/less than or equal to a number other than 1. The following statement will never return a row. SELECT ename FROM emp WHERE ROWNUM = 2; When the first row is read from the emp table, the predicate is applied. Since this is the first row, it is conditionally assigned ROWNUM of 1. However, the condition is that ROWNUM must be equal to 2. Since this condition is not met, the row is not placed in the result set. The second row is read. Since the result set is empty, this row is assigned ROWNUM of 1. Once again, the condition is not met and the row is not placed in the result set. This process repeats until all the rows are read. However, the condition never evaluates to TRUE, so no rows are placed in the result set. Try the following SELECT r, GENDER FROM (SELECT ROWNUM r, GENDER FROM EMP2 WHERE ROWNUM = 20) WHERE r 10; *In the example below, ROWNUM is evaluated by the outer query as the ROWNUM of the outer query, not the column ROWNUM in the inner query. MaryAnn Atkinson wrote: I definitely dont fully understand ROWNUM yet, and you guys so far provided more info than a couple of books by Oracle, that I have here. For a second I thought I'm beginning to get it, but the queries suggested produced no results... SQL SELECT ROWNUM, GENDER 2 FROM (SELECT ROWNUM, GENDER 3 FROM EMP2 4 WHERE ROWNUM = 20) 5 WHERE ROWNUM 10; no rows selected SQL SELECT r, GENDER 2 FROM (SELECT ROWNUM r, GENDER 3 FROM EMP2 4 WHERE ROWNUM = 20) 5 WHERE ROWNUM 10; no rows selected SQL SELECT r ROWNUM, GENDER 2 FROM (SELECT ROWNUM r, GENDER 3 FROM EMP2 4 WHERE ROWNUM = 20) 5 WHERE ROWNUM 10; no rows selected SQL ... so, any help is appreciated - rownum is driving me nuts... thx maa __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: MaryAnn Atkinson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).begin:vcard n:Fink;Daniel tel;cell:303.808.3282 tel;work:303.272.3225 x-mozilla-html:TRUE adr:;; version:2.1 email;internet:[EMAIL PROTECTED] title:DB Services Lead x-mozilla-cpt:;-4832 fn:Daniel Fink end:vcard
RE: ROWNUM -- HOW ARE ROWS SELECTED?
Its obvious I hadnt fully understood ROWNUM yet, as you see we are learning bits and pieces as we go along. Help me out here, will you? Talk to me like I'm a 10-year old, its ok. MaryAnn, the best way to understand rownum is to do the following: SQL SELECT ROWNUM, GENDER 2 FROM (SELECT ROWNUM, GENDER 3 FROM EMP2 4 WHERE ROWNUM = 20) You will quickly see that, no matter how you order the result set, the first record returned is rownum #1, second is rownum #2 etc. The rownum value is assigned as rows are RETURNED or DISPLAYED, not as they are selected. Fine, the rows are numbered as returned or displayed(not selected). BUT THEN HOW ARE THEY SELECTED? My emp2 table has 1+ rows in it, how are only 20 selected? Based on what criteria? How do I get 20 back? I want to understand this first. Then, once I get these 20 back, then fine, they are numbered starting from 1, that part I kind of figured it out, or so I think. The part I dont get, is HOW ARE THEY SELECTED? thx maa __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: MaryAnn Atkinson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: ROWNUM is driving me nuts - queries suggested produced no res
Think of it this way: when Oracle builds a result set, it goes into the table and tries to qualify the records in the table by evaluating the where clause; if the record does not qualify, the record will not appear in the result set, if the record does qualify, then the record will be returned in the result set. Once a record is qualified and is eligible to be included in the result set, Oracle assigns it a ROWNUM value, which starts at 1 and is contiguous. In your several examples below, the result set from the subquery is irrelevant because your enclosing query has ROWNUM 10, which is applied to the result set arising from your enclosing query. In a previous email, someone (who I don't remember) explained that you can't select a set of records from a result set unless your criteria for the ROWNUM starts-at and includes ROWNUM 1. (A recap: let's go with your ROWNUM 10: once a record is otherwise qualified, Oracle will assign it ROWNUM of 1 for the first record, then it will have to re-qualify the record base on ROWNUM 10; since the assigned ROWNUM is 1, it fails the ROWNUM 10 so this record is discarded. On the second record, Oracle will re-assign the ROWNUM as 1 because ROWNUMs are contiguous; it does the same evaluation of whether the assigned ROWNUM is greater than 10, and failing which, the record is again discarded. In fact, no records will pass the ROWNUM 10 because every previous record was discarded, so the assigned ROWNUM value is always 1 when Oracle tries to evaluate whether the record's ROWNUM 10) As a result, your reference to ROWNUM 10 will always produce zero records, irrespective of what the subquery may or may not return. The solution you probably want is to alias the ROWNUM column in the subquery, and then reference the aliased column name rather than the ROWNUM from the enclosing query: select r, GENDER from (select ROWNUM r, GENDER from EMP2 where ROWNUM = 20) where r 10; You can see the differences in the ROWNUM with this: select ROWNUM, -- this is from the enclosing query R, -- this is from the subquery GENDER from (select ROWNUM R, GENDER from EMP2 where ROWNUM = 20) where r 10; ..Rudy -Original Message- Sent: Wednesday, July 09, 2003 12:19 PM To: Multiple recipients of list ORACLE-L I definitely dont fully understand ROWNUM yet, and you guys so far provided more info than a couple of books by Oracle, that I have here. For a second I thought I'm beginning to get it, but the queries suggested produced no results... SQL SELECT ROWNUM, GENDER 2 FROM (SELECT ROWNUM, GENDER 3 FROM EMP2 4 WHERE ROWNUM = 20) 5 WHERE ROWNUM 10; no rows selected SQL SELECT r, GENDER 2 FROM (SELECT ROWNUM r, GENDER 3 FROM EMP2 4 WHERE ROWNUM = 20) 5 WHERE ROWNUM 10; no rows selected SQL SELECT r ROWNUM, GENDER 2 FROM (SELECT ROWNUM r, GENDER 3 FROM EMP2 4 WHERE ROWNUM = 20) 5 WHERE ROWNUM 10; no rows selected SQL -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rudy Zung INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: ROWNUM - THATS THE ONE!!! THAT DID IT!!!
THATS THE ONE!!! THAT DID IT!!! --- Freeman Robert - IL [EMAIL PROTECTED] wrote: To add on to my previous comments, change your code a bit: SQL SELECT RN, GENDER 2 FROM (SELECT ROWNUM RN, GENDER 3 FROM EMP2 4 WHERE ROWNUM = 20) 5 WHERE RN 10; The last WHERE is testing on RN, not ROWNUM. Thats the trick right there! If its true that we can only test and = for ROWNUM, and that sure appears to be the case, then we cannot have ROWNUM on the last WHERE clause on line 5, but its alias is fine. Robert Freeman? I had a professor Robert Freeman, he taught Comp Engr at UMC... Many thanks, maa __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: MaryAnn Atkinson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: ROWNUM is driving me nuts - queries suggested produced no results
I definitely dont fully understand ROWNUM yet, and you guys so far provided more info than a couple of books by Oracle, that I have here. For a second I thought I'm beginning to get it, but the queries suggested produced no results... SQL SELECT ROWNUM, GENDER 2 FROM (SELECT ROWNUM, GENDER 3 FROM EMP2 4 WHERE ROWNUM = 20) 5 WHERE ROWNUM 10; no rows selected All right, what does the INNER query return? The 20 first rows it gathers from Oracle. This is an inline view, so you can consider this to be a new table we are returning rows from at the OUTER level. The snag with ROWNUM is that it is a reserved word. So when Oracle parses the query, it considers that you want the rows (for the OUTER query) starting from the 11th - by virtue of the 'counting-on-the-fly' nature of ROWNUM, you never get any row since the first one you could get would be numbered 1, as has already been explained to you. What do you really want, at the OUTER level? You want the INNER rownum to be greater than 10, not the OUTER, current rownum. What you need to understand is that once you jump from an inner to an outer queries, columns, whether they are true ones, computed ones or pseudo ones, from the inner query take a life of their own. The trick is to rename the (pseudo) column of the inner query (I return both ROWNUMs so that you can check how they differ) : SQL SELECT ROWNUM OUTER_ROWNUM, INNER_ROWNUM, GENDER 2 FROM (SELECT ROWNUM INNER_ROWNUM, GENDER 3 FROM EMP2 4 WHERE ROWNUM = 20) 5 WHERE INNER_ROWNUM 10; Once you have understood this, the rest will come naturally ... I still believe that it's easier and faster to expand the RBS tablespace ... Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: ROWNUM -- HOW ARE ROWS SELECTED?
Mary Ann, Assume that the rows are selected in random order. Primary Keys and Order By exist for ordering and qualifying your data. If you just select * from EMP2, most likely the rows willl come back in the order that they were inserted, but this is not necessarily true, especially if data has been deleted from the table. Add a column that is a sequence number or a date/time stamp, then order by that column. select emp_sequence_no,gender from emp where emp_sequence_no = 20 order by emp_sequence_no; Jay Hostetter Oracle DBA D. E. Communications Ephrata, PA USA [EMAIL PROTECTED] 07/09/03 12:44PM Its obvious I hadnt fully understood ROWNUM yet, as you see we are learning bits and pieces as we go along. Help me out here, will you? Talk to me like I'm a 10-year old, its ok. MaryAnn, the best way to understand rownum is to do the following: SQL SELECT ROWNUM, GENDER 2 FROM (SELECT ROWNUM, GENDER 3 FROM EMP2 4 WHERE ROWNUM = 20) You will quickly see that, no matter how you order the result set, the first record returned is rownum #1, second is rownum #2 etc. The rownum value is assigned as rows are RETURNED or DISPLAYED, not as they are selected. Fine, the rows are numbered as returned or displayed(not selected). BUT THEN HOW ARE THEY SELECTED? My emp2 table has 1+ rows in it, how are only 20 selected? Based on what criteria? How do I get 20 back? I want to understand this first. Then, once I get these 20 back, then fine, they are numbered starting from 1, that part I kind of figured it out, or so I think. The part I dont get, is HOW ARE THEY SELECTED? thx maa **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: ROWNUM -- HOW ARE ROWS SELECTED?
Oracle does not guarantee the order in which records are retrieved (read in from the disk or data buffers) nor does Oracle guarantee the order in which records are presented to you unless you specify an ORDER BY. The ROWNUM is assigned after Oracle has selected the record to appear in the result set, however the initial selection of the record is in whatever order Oracle deems to be most efficient in retrieving your records, and the ROWNUM is assigned after the record has passed the WHERE clause evaluation, and as a last step, the ORDER BY is applied to sort the order of presentation of the records to you. ..Rudy -Original Message- Sent: Wednesday, July 09, 2003 12:45 PM To: Multiple recipients of list ORACLE-L Its obvious I hadnt fully understood ROWNUM yet, as you see we are learning bits and pieces as we go along. Help me out here, will you? Talk to me like I'm a 10-year old, its ok. MaryAnn, the best way to understand rownum is to do the following: SQL SELECT ROWNUM, GENDER 2 FROM (SELECT ROWNUM, GENDER 3 FROM EMP2 4 WHERE ROWNUM = 20) You will quickly see that, no matter how you order the result set, the first record returned is rownum #1, second is rownum #2 etc. The rownum value is assigned as rows are RETURNED or DISPLAYED, not as they are selected. Fine, the rows are numbered as returned or displayed(not selected). BUT THEN HOW ARE THEY SELECTED? My emp2 table has 1+ rows in it, how are only 20 selected? Based on what criteria? How do I get 20 back? I want to understand this first. Then, once I get these 20 back, then fine, they are numbered starting from 1, that part I kind of figured it out, or so I think. The part I dont get, is HOW ARE THEY SELECTED? thx maa __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: MaryAnn Atkinson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rudy Zung INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: ROWNUM - Can the inner-outer idea be extended to an update?
Can the inner-outer idea be extended to an update? UPDATE EMP2 SETGENDER = 'F' WHERE GENDER = ' ' ANDROWNUM BETWEEN 10 AND 20; That does NOT work, because as we explained the returned rows are numbered starting from 1, and so rownum never really matches a 10 or 20 or anything in between. Thanks, maa __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: MaryAnn Atkinson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Re: ROWNUM - Can the inner-outer idea be extended to an update?
IF you are looking to break up the update into more 'manageable' pieces, here is a thought... UPDATE EMP2 SET GENDER = 'F' WHERE GENDER = ' ' AND ROWNUM = 100; UPDATE EMP2 SET GENDER = 'F' WHERE GENDER = ' ' AND ROWNUM = 100; *Since the first 100 records have already been updated, the second update will update the next 100. Run the statement several until you have updated all the records concerned. You can commit in between or not, as long as you do all this as one session. MaryAnn Atkinson wrote: Can the inner-outer idea be extended to an update? UPDATE EMP2 SETGENDER = 'F' WHERE GENDER = ' ' ANDROWNUM BETWEEN 10 AND 20; That does NOT work, because as we explained the returned rows are numbered starting from 1, and so rownum never really matches a 10 or 20 or anything in between. Thanks, maa __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: MaryAnn Atkinson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).begin:vcard n:Fink;Daniel tel;cell:303.808.3282 tel;work:303.272.3225 x-mozilla-html:TRUE adr:;; version:2.1 email;internet:[EMAIL PROTECTED] title:DB Services Lead x-mozilla-cpt:;-4832 fn:Daniel Fink end:vcard
RE: ROWNUM is driving me nuts - queries suggested produced no res
just what is it you want to return in your query? If the query results are to be displayed a chunk at a time on a web page then you should give strong consideration to using OCI and implementing scrollable cursors with the OCIStmtFetch2() function and its OCI_FETCH_NEXT, OCI_FETCH_PRIOR, OCI_FETCH_FIRST, OCI_FETCH_LAST, OCI_FETCH_ABSOLUTE orientation parameters and nrows parameter. This is Oracle9i only. With Oracle8i you can use OCI_FETCH_NEXT with nrows but you can't go backwards. Oracle recommends migrating all OCIStmtFetch() calls to OCIStmtFetch2() calls. Steve Orr Bozeman, Montana -Original Message- Sent: Wednesday, July 09, 2003 10:35 AM To: Multiple recipients of list ORACLE-L res MaryAnn, the best way to understand rownum is to do the following: SQL SELECT ROWNUM, GENDER 2 FROM (SELECT ROWNUM, GENDER 3 FROM EMP2 4 WHERE ROWNUM = 20) You will quickly see that, no matter how you order the result set, the first record returned is rownum #1, second is rownum #2 etc. The rownum value is assigned as rows are RETURNED or DISPLAYED, not as they are selected. when you run your original query: SQL SELECT ROWNUM, GENDER 2 FROM (SELECT ROWNUM, GENDER 3 FROM EMP2 4 WHERE ROWNUM = 20) 5 WHERE ROWNUM 10; you should *not* get any rows because the first row returned has a value of 1 - hece you get nothing. the next row returned gets the value of 1 - again, nothing gets shown. just what is it you want to return in your query? Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, July 09, 2003 12:19 PM To: Multiple recipients of list ORACLE-L results I definitely dont fully understand ROWNUM yet, and you guys so far provided more info than a couple of books by Oracle, that I have here. For a second I thought I'm beginning to get it, but the queries suggested produced no results... SQL SELECT ROWNUM, GENDER 2 FROM (SELECT ROWNUM, GENDER 3 FROM EMP2 4 WHERE ROWNUM = 20) 5 WHERE ROWNUM 10; no rows selected SQL SELECT r, GENDER 2 FROM (SELECT ROWNUM r, GENDER 3 FROM EMP2 4 WHERE ROWNUM = 20) 5 WHERE ROWNUM 10; no rows selected SQL SELECT r ROWNUM, GENDER 2 FROM (SELECT ROWNUM r, GENDER 3 FROM EMP2 4 WHERE ROWNUM = 20) 5 WHERE ROWNUM 10; no rows selected SQL ... so, any help is appreciated - rownum is driving me nuts... thx maa __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: MaryAnn Atkinson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Re: RE: ROWNUM - Can the inner-outer idea be extended to an update?
next tested it but id guess. dont know about 8i. why would you want to do that? if you post what your goal is, ther emay be a better method. UPDATE EMP2 SETGENDER = 'F' WHERE GENDER = (Select gender.(nested stuff) here. same as from that link. From: MaryAnn Atkinson [EMAIL PROTECTED] Date: 2003/07/09 Wed PM 01:34:24 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: ROWNUM - Can the inner-outer idea be extended to an update? Can the inner-outer idea be extended to an update? UPDATE EMP2 SETGENDER = 'F' WHERE GENDER = ' ' ANDROWNUM BETWEEN 10 AND 20; That does NOT work, because as we explained the returned rows are numbered starting from 1, and so rownum never really matches a 10 or 20 or anything in between. Thanks, maa __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: MaryAnn Atkinson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: Rownum
just do select rownum, col1,col2, from table1 -Message d'origine-De: Santosh Varma [mailto:[EMAIL PROTECTED]Envoyé: mercredi 19 mars 2003 09:44À: Multiple recipients of list ORACLE-LObjet: Rownum Hello list, I wanted to know the concept of Rownum.. Is it similar to Row Id ? Thanks and Regards, Santosh
Re: Rownum
Santosh, Good question. Actually these 2 are not related. Rowid is an actual attribute assigned to each row of a table and is unique. Rownum is logically assigned to rows returned in a query (nothing to do with the table). It is assigned sequentially as rows are returned (meaning if you can't have a rownum 2 until you've gotten a rownum 1). Ex. SQL select rowid from dual; ROWID -- CsAABGkAAA SQL select rownum from dual; ROWNUM -- 1 SQL select rownum from dual where rownum=2; no rows selected SQL select rownum from dual where rownum 2; ROWNUM -- 1 Hope this was helpful, Darrell [EMAIL PROTECTED] 03/19/03 03:43AM Hello list, I wanted to know the concept of Rownum.. Is it similar to Row Id ? Thanks and Regards, Santosh -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Darrell Landrum INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: Rownum
no, rownum is a sequential number whereas rowid is a pointer to the row of the table within the database. -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Santosh VarmaSent: Wednesday, March 19, 2003 4:44 AMTo: Multiple recipients of list ORACLE-LSubject: Rownum Hello list, I wanted to know the concept of Rownum.. Is it similar to Row Id ? Thanks and Regards, Santosh
Re: Re: Rownum
Darrell Landrum,ÄúºÃ£¡ thanx for your explaination. I wonder :whether i can judge the select return. I find the rowNUM is not 0, is right? === 2003-03-19 05:03:00 ÄúÔÚÀ´ÐÅÖÐдµÀ£º=== Santosh, Good question. Actually these 2 are not related. Rowid is an actual attribute assigned to each row of a table and is unique. Rownum is logically assigned to rows returned in a query (nothing to do with the table). It is assigned sequentially as rows are returned (meaning if you can't have a rownum 2 until you've gotten a rownum 1). Ex. SQL select rowid from dual; ROWID -- CsAABGkAAA SQL select rownum from dual; ROWNUM -- 1 SQL select rownum from dual where rownum=2; no rows selected SQL select rownum from dual where rownum 2; ROWNUM -- 1 Hope this was helpful, Darrell [EMAIL PROTECTED] 03/19/03 03:43AM Hello list, I wanted to know the concept of Rownum.. Is it similar to Row Id ? Thanks and Regards, Santosh -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Darrell Landrum INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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). = = = = = = = = = = = = = = = = = = = = Ö Àñ£¡ Áõ¼Ì¶« [EMAIL PROTECTED] 2003-03-19 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?GB2312?Q?=C1=F5=BC=CC=B6=AB?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: Rownum
No. It is not similar to a rowid. It is just a pseudo sequential number assigned to the rows in the result set (after the result set is prepared) from the query. - Kirti -Original Message- Sent: Wednesday, March 19, 2003 3:44 AM To: Multiple recipients of list ORACLE-L Hello list, I wanted to know the concept of Rownum.. Is it similar to Row Id ? Thanks and Regards, Santosh -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Re: Re: Rownum
That's correct, there's no rownum of 0. ex. SQL select rownum, username from dba_users; ROWNUM USERNAME -- -- 1 SYS 2 SYSTEM 3 OUTLN 4 OEM_DLAND 5 DBSNMP 6 HR 7 PM 8 DLANDRUM 9 OE 10 WMSYS 11 ORDSYS 12 ORDPLUGINS 13 MDSYS 14 CTXSYS 15 QS_CS 16 QS_CB 17 QS_CBADM 18 QS_OS 19 QS_ES 20 QS_WS 21 QS 22 QS_ADM 23 SH 24 XDB 25 ANONYMOUS 26 WKSYS 27 WKPROXY 28 ODM 29 ODM_MTR 30 OLAPSYS 30 rows selected. I'm not sure what you mean by 'judge the select return', but the only real use I can think of for rownum is to restrict your query to a small random sampling of your data... ex. SQL select table_name, tablespace_name from dba_tables 2 where rownum 5; TABLE_NAME TABLESPACE_NAME -- -- SEG$ SYSTEM CLU$ SYSTEM OBJ$ SYSTEM FILE$ SYSTEM [EMAIL PROTECTED] 03/19/03 08:33AM Darrell Landrum,-? +úí thanx for your explaination. I wonder :whether i can judge the select return. I find the rowNUM is not 0, is right? === 2003-03-19 05:03:00 -?+++ -++-- +ú === Santosh, Good question. Actually these 2 are not related. Rowid is an actual attribute assigned to each row of a table and is unique. Rownum is logically assigned to rows returned in a query (nothing to do with the table). It is assigned sequentially as rows are returned (meaning if you can't have a rownum 2 until you've gotten a rownum 1). Ex. SQL select rowid from dual; ROWID -- CsAABGkAAA SQL select rownum from dual; ROWNUM -- 1 SQL select rownum from dual where rownum=2; no rows selected SQL select rownum from dual where rownum 2; ROWNUM -- 1 Hope this was helpful, Darrell [EMAIL PROTECTED] 03/19/03 03:43AM Hello list, I wanted to know the concept of Rownum.. Is it similar to Row Id ? Thanks and Regards, Santosh -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Darrell Landrum INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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). = = = = = = = = = = = = = = = = = = = = +- +±úí -)+ ½ [EMAIL PROTECTED] 2003-03-19 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?GB2312?Q?=C1=F5=BC=CC=B6=AB?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Darrell Landrum INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: Rownum
Thanks to all who replied to me. Now it is clear.. santosh -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of John BlakeSent: Wednesday, March 19, 2003 7:15 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Rownum no, rownum is a sequential number whereas rowid is a pointer to the row of the table within the database. -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Santosh VarmaSent: Wednesday, March 19, 2003 4:44 AMTo: Multiple recipients of list ORACLE-LSubject: Rownum Hello list, I wanted to know the concept of Rownum.. Is it similar to Row Id ? Thanks and Regards, Santosh
RE: ROWNUM strangeness?
Post your query -Original Message- Sent: Thursday, August 29, 2002 9:23 PM To: Multiple recipients of list ORACLE-L I've been given a query to investigate. The performance varies extremely depending on whether the entire result set is fetched or a ROWNUM n clause is attached; that makes sense to me. What doesn't make sense is the following: if we add WHERE ROWNUM 50, the query returns 24 rows. If we add WHERE ROWNUM 1000, the query returns 336 rows! I don't know the size of the entire result set (I'm running a count right now), but if the result set consists of at least 336 rows, shouldn't WHERE ROWNUM 50 return 49 rows? TIA! = Paul Baumgartel, Adept Computer Associates, Inc. [EMAIL PROTECTED] __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Naveen Nahata 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).
Re: ROWNUM strangeness?
Holes in your extents/segments. Normal. You get this after repeated updates/deletes/inserts. ltiu Paul Baumgartel wrote: I've been given a query to investigate. The performance varies extremely depending on whether the entire result set is fetched or a ROWNUM n clause is attached; that makes sense to me. What doesn't make sense is the following: if we add WHERE ROWNUM 50, the query returns 24 rows. If we add WHERE ROWNUM 1000, the query returns 336 rows! I don't know the size of the entire result set (I'm running a count right now), but if the result set consists of at least 336 rows, shouldn't WHERE ROWNUM 50 return 49 rows? TIA! = Paul Baumgartel, Adept Computer Associates, Inc. [EMAIL PROTECTED] __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu 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).
RE: ROWNUM strangeness?
Hi Paul, How's going? Maybe there are other criteria's that limits the result set to only return 24 rows? Richard Ji -Original Message- Sent: Thursday, August 29, 2002 11:53 AM To: Multiple recipients of list ORACLE-L I've been given a query to investigate. The performance varies extremely depending on whether the entire result set is fetched or a ROWNUM n clause is attached; that makes sense to me. What doesn't make sense is the following: if we add WHERE ROWNUM 50, the query returns 24 rows. If we add WHERE ROWNUM 1000, the query returns 336 rows! I don't know the size of the entire result set (I'm running a count right now), but if the result set consists of at least 336 rows, shouldn't WHERE ROWNUM 50 return 49 rows? TIA! = Paul Baumgartel, Adept Computer Associates, Inc. [EMAIL PROTECTED] __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ji, Richard 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).
Re: ROWNUM strangeness?
Actually. Scratch my previous email on this matter. We have seen this problem before and I couldn't figure out how to fix it. ltiu Paul Baumgartel wrote: I've been given a query to investigate. The performance varies extremely depending on whether the entire result set is fetched or a ROWNUM n clause is attached; that makes sense to me. What doesn't make sense is the following: if we add WHERE ROWNUM 50, the query returns 24 rows. If we add WHERE ROWNUM 1000, the query returns 336 rows! I don't know the size of the entire result set (I'm running a count right now), but if the result set consists of at least 336 rows, shouldn't WHERE ROWNUM 50 return 49 rows? TIA! = Paul Baumgartel, Adept Computer Associates, Inc. [EMAIL PROTECTED] __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu 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).
RE: ROWNUM strangeness?--SOLVED
As we all know, ROWNUM is *usually* assigned when rows are read into the cache, before sorting; Always -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Naveen Nahata 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).
Re: ROWNUM strangeness?
Paul Baumgartel wrote: I've been given a query to investigate. The performance varies extremely depending on whether the entire result set is fetched or a ROWNUM n clause is attached; that makes sense to me. What doesn't make sense is the following: if we add WHERE ROWNUM 50, the query returns 24 rows. If we add WHERE ROWNUM 1000, the query returns 336 rows! I don't know the size of the entire result set (I'm running a count right now), but if the result set consists of at least 336 rows, shouldn't WHERE ROWNUM 50 return 49 rows? TIA! = Paul Baumgartel, Adept Computer Associates, Inc. [EMAIL PROTECTED] Wow! a nice case ! Could you please post the query ? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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).
RE: ROWNUM strangeness?
Title: RE: ROWNUM strangeness? show the full sql please, perhaps its how you are grouping your conditions Paul Baumgartel wrote: I've been given a query to investigate. The performance varies extremely depending on whether the entire result set is fetched or a ROWNUM n clause is attached; that makes sense to me. What doesn't make sense is the following: if we add WHERE ROWNUM 50, the query returns 24 rows. If we add WHERE ROWNUM 1000, the query returns 336 rows! I don't know the size of the entire result set (I'm running a count right now), but if the result set consists of at least 336 rows, shouldn't WHERE ROWNUM 50 return 49 rows? TIA! = Paul Baumgartel, Adept Computer Associates, Inc. [EMAIL PROTECTED] __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu 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).
RE: ROWNUM strangeness?
Paul, It's because of how ROWNUM is interpreted. The first query only returned the first 50 rows it found and you got 24 that passed the rest of your criteria. The second query returned 1000 rows of which only 336 passed the rest of your criteria. Makes perfect sense to me. This is why ROWNUM can cause confusing results. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, August 29, 2002 11:53 AM To: Multiple recipients of list ORACLE-L I've been given a query to investigate. The performance varies extremely depending on whether the entire result set is fetched or a ROWNUM n clause is attached; that makes sense to me. What doesn't make sense is the following: if we add WHERE ROWNUM 50, the query returns 24 rows. If we add WHERE ROWNUM 1000, the query returns 336 rows! I don't know the size of the entire result set (I'm running a count right now), but if the result set consists of at least 336 rows, shouldn't WHERE ROWNUM 50 return 49 rows? TIA! = Paul Baumgartel, Adept Computer Associates, Inc. [EMAIL PROTECTED] __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F 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).
Re: ROWNUM strangeness?
I believe you have ROWNUM confused with ROWID. --- ltiu [EMAIL PROTECTED] wrote: Holes in your extents/segments. Normal. You get this after repeated updates/deletes/inserts. ltiu Paul Baumgartel wrote: I've been given a query to investigate. The performance varies extremely depending on whether the entire result set is fetched or a ROWNUM n clause is attached; that makes sense to me. What doesn't make sense is the following: if we add WHERE ROWNUM 50, the query returns 24 rows. If we add WHERE ROWNUM 1000, the query returns 336 rows! I don't know the size of the entire result set (I'm running a count right now), but if the result set consists of at least 336 rows, shouldn't WHERE ROWNUM 50 return 49 rows? TIA! = Paul Baumgartel, Adept Computer Associates, Inc. [EMAIL PROTECTED] __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu 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). __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel 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).
RE: ROWNUM strangeness?
Actually, ROWNUM has nothing to do with extents/segments/inserts/updates/deletes. It is all about access paths. The solution posted makes sense as ROWNUM can be affected by many subsequent operations. -Original Message- Sent: Thursday, August 29, 2002 11:04 AM To: Multiple recipients of list ORACLE-L Holes in your extents/segments. Normal. You get this after repeated updates/deletes/inserts. ltiu Paul Baumgartel wrote: I've been given a query to investigate. The performance varies extremely depending on whether the entire result set is fetched or a ROWNUM n clause is attached; that makes sense to me. What doesn't make sense is the following: if we add WHERE ROWNUM 50, the query returns 24 rows. If we add WHERE ROWNUM 1000, the query returns 336 rows! I don't know the size of the entire result set (I'm running a count right now), but if the result set consists of at least 336 rows, shouldn't WHERE ROWNUM 50 return 49 rows? TIA! = Paul Baumgartel, Adept Computer Associates, Inc. [EMAIL PROTECTED] __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Fink, Dan 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).
RE: ROWNUM strangeness?--SOLVED
To be exact... Rownum is sequentially assigned for each row that meets the predicate. If the row does not match the predicate condition, it is discarded and not assigned a rownum. Below is an example using our friendly EMP table. As for sorting, rownum is assigned before any sorting. However, if the data is read via an index, the rownum appears to be assigned after the sort. SQL l 1 select rownum, empno, ename, deptno 2* from emp SQL / ROWNUM EMPNO ENAME DEPTNO -- -- -- -- 1 7369 SMITH 20 2 7499 ALLEN 30 3 7521 WARD 30 4 7566 JONES 20 5 7654 MARTIN 30 6 7698 BLAKE 30 7 7782 CLARK 10 8 7788 SCOTT 20 9 7839 KING 10 10 7844 TURNER 30 11 7876 ADAMS 20 12 7900 JAMES 30 13 7902 FORD 20 14 7934 MILLER 10 15 null1 16 9998 null2 16 rows selected. SQL 2 2* from emp SQL i 3 where rownum 5 4 SQL l 1 select rownum, empno, ename, deptno 2 from emp 3* where rownum 5 SQL / ROWNUM EMPNO ENAME DEPTNO -- -- -- -- 1 7369 SMITH 20 2 7499 ALLEN 30 3 7521 WARD 30 4 7566 JONES 20 1 select rownum, empno, ename, deptno 2 from emp 3 where rownum 5 4* and deptno = 30 SQL / ROWNUM EMPNO ENAME DEPTNO -- -- -- -- 1 7499 ALLEN 30 2 7521 WARD 30 3 7654 MARTIN 30 4 7698 BLAKE 30 1 select rownum, empno, ename, deptno 2 from emp 3 where rownum 5 4and deptno = 30 5* order by ename SQL / ROWNUM EMPNO ENAME DEPTNO -- -- -- -- 1 7499 ALLEN 30 4 7698 BLAKE 30 3 7654 MARTIN 30 2 7521 WARD 30 -Original Message- Sent: Thursday, August 29, 2002 11:44 AM To: Multiple recipients of list ORACLE-L As we all know, ROWNUM is *usually* assigned when rows are read into the cache, before sorting; Always -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Naveen Nahata 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Fink, Dan 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).
Re: ROWNUM strangeness?
I agree. I got confused and my second email to disregard my erroneous first emai on this matterl was apparently shot down by the mail filters so it never got to you guys in time. Sorry for the confision. ltiu Fink, Dan wrote: Actually, ROWNUM has nothing to do with extents/segments/inserts/updates/deletes. It is all about access paths. The solution posted makes sense as ROWNUM can be affected by many subsequent operations. -Original Message- Sent: Thursday, August 29, 2002 11:04 AM To: Multiple recipients of list ORACLE-L Holes in your extents/segments. Normal. You get this after repeated updates/deletes/inserts. ltiu Paul Baumgartel wrote: I've been given a query to investigate. The performance varies extremely depending on whether the entire result set is fetched or a ROWNUM n clause is attached; that makes sense to me. What doesn't make sense is the following: if we add WHERE ROWNUM 50, the query returns 24 rows. If we add WHERE ROWNUM 1000, the query returns 336 rows! I don't know the size of the entire result set (I'm running a count right now), but if the result set consists of at least 336 rows, shouldn't WHERE ROWNUM 50 return 49 rows? TIA! = Paul Baumgartel, Adept Computer Associates, Inc. [EMAIL PROTECTED] __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu 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).
Re: ROWNUM strangeness?
Yes sir. Paul Baumgartel wrote: I believe you have ROWNUM confused with ROWID. --- ltiu [EMAIL PROTECTED] wrote: Holes in your extents/segments. Normal. You get this after repeated updates/deletes/inserts. ltiu Paul Baumgartel wrote: I've been given a query to investigate. The performance varies extremely depending on whether the entire result set is fetched or a ROWNUM n clause is attached; that makes sense to me. What doesn't make sense is the following: if we add WHERE ROWNUM 50, the query returns 24 rows. If we add WHERE ROWNUM 1000, the query returns 336 rows! I don't know the size of the entire result set (I'm running a count right now), but if the result set consists of at least 336 rows, shouldn't WHERE ROWNUM 50 return 49 rows? TIA! = Paul Baumgartel, Adept Computer Associates, Inc. [EMAIL PROTECTED] __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu 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). __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu 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).
RE: ROWNUM strangeness?
It is not the predicate that causes the behavior, but the distinct. I'm taking an educated guess here, so please correct me if I am wrong. As the row is read, the predicate is applied. If the row matches the predicate, a rownum is assigned and it is saved as part of the result set. Once the rownum criteria is met, all subsequent rows are discarded as not meeting the criteria or oracle stops processing. I'm not sure which, but a few tests could confirm which is true. Regardless, the result is the same. After all matching rows have been placed in the result set, the DISTINCT operation sorts the data and discards any duplicates. As such, no new rows are read to match the 'rownum' criteria. SQL select deptno, job 2 from emp 3 where rownum 5; DEPTNO JOB -- - 20 CLERK 30 SALESMAN 30 SALESMAN 20 MANAGER SQL edit Wrote file afiedt.buf 1 select distinct deptno, job 2 from emp 3* where rownum 5 SQL / DEPTNO JOB -- - 20 CLERK 20 MANAGER 30 SALESMAN -Original Message- Sent: Thursday, August 29, 2002 12:33 PM To: Multiple recipients of list ORACLE-L Paul, It's because of how ROWNUM is interpreted. The first query only returned the first 50 rows it found and you got 24 that passed the rest of your criteria. The second query returned 1000 rows of which only 336 passed the rest of your criteria. Makes perfect sense to me. This is why ROWNUM can cause confusing results. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, August 29, 2002 11:53 AM To: Multiple recipients of list ORACLE-L I've been given a query to investigate. The performance varies extremely depending on whether the entire result set is fetched or a ROWNUM n clause is attached; that makes sense to me. What doesn't make sense is the following: if we add WHERE ROWNUM 50, the query returns 24 rows. If we add WHERE ROWNUM 1000, the query returns 336 rows! I don't know the size of the entire result set (I'm running a count right now), but if the result set consists of at least 336 rows, shouldn't WHERE ROWNUM 50 return 49 rows? TIA! = Paul Baumgartel, Adept Computer Associates, Inc. [EMAIL PROTECTED] __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Fink, Dan 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).
Re: ROWNUM strangeness?--SOLVED
As for sorting, rownum is assigned before any sorting. However, if the data is read via an index, the rownum appears to be assigned after the sort. Dan, I wouldn't say that. What *appears* is the sort, which doesn't take place since data is fetched in sorted order. Rownum is assigned in the order data is fetched, as you say elsewhere. I am pretty sure it's clear in your mind, but it may sound confusing to others. When Naveen said that the rows is ALWAYS assigned before sorting, it's true, even in that case since there is no sort (in the 'shuffling of things in memory' acceptance of the word). -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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).
Re: ROWNUM question ???
Janet Linsy wrote: Hi, I have a table like this: SQL select * from test; ID PRICE -- -- 1 1 1 3 1 4 1 5 1 6 I need to get the second largest price and I was using the ROWNUM. For the following statement, the result makes sense: SQL select * from test where rownum (select count(*) -1 from test); ID PRICE -- -- 1 1 2 3 3 4 But the rest twos didn't return anything: SQL select * from test where rownum=(select count(*) -1 from test); -- supposed to return 5 ??? no rows selected SQL select * from test where rownum (select count(*) -1 from test); -- supposed to return 6 ??? no rows selected I vaguely remember that ROWNUM only works in case, why is that? Thank you! Janet, The reason is that it is computed on the fly as rows are returned (i.e. pass all filters). Rownum=1 works, but rownum=2 cannot, since to have a rownum value of 2 you should have first displayed a rownum 1. Catch 22. or = works, for the same reason. To answer your question, you have several ways to do it. I have a test table slightly different from yours : SQL select * from test; ID PRICE -- -- 11.5 21.8 3 2 41.9 51.3 The rownum is computed BEFORE any ORDER BY : 1 select rownum, id, price 2* from test SQL / ROWNUM ID PRICE -- -- -- 1 11.5 2 21.8 3 3 2 4 41.9 5 51.3 1 select rownum, id, price 2 from test 3* order by price desc SQL / ROWNUM ID PRICE -- -- -- 3 3 2 4 41.9 2 21.8 1 11.5 5 51.3 However, you can cheat by having the ORDER BY performed inside an in-line view : 1 select rownum, x.id, x.price 2 from (select id, price 3from test 4* order by price desc) x SQL / ROWNUM ID PRICE -- -- -- 1 3 2 2 41.9 3 21.8 4 11.5 5 51.3 By nesting one degree deeper, you can answer your question (well, fairly) easily : 1 select y.id, y.price 2 from (select rownum price_rank, x.id, x.price 3from (select id, price 4 from test 5 order by price desc) x) y 6* where y.price_rank = 2 SQL / ID PRICE -- -- 41.9 Here, the rownum has been computed on the fly, but INSIDE the least nested in-line view, so it appears as 'static' data at the outside level and then = or works. Another way to do it is this : 1 select x.id, x.price 2 from (select id, price 3from test 4order by price desc) x 5 where x.price (select max(price) 6 from test) 7* and rownum = 1 SQL / ID PRICE -- -- 41.9 which is likely to be more efficient, especially if PRICE is indexed. I have tried to have a go with the RANK() analytical function, but without much success :-(. HTH, Stephane Faroult Oriole Ltd -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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).
