Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Select First 500 Rows of a Table
melliott42_at_yahoo.com (Michael) wrote in message news:<91721cf.0408180230.570e49fe_at_posting.google.com>...
> Hello all,
>
> Thanks for responding.
>
> It seems Oracle keeps track of records\rows internally using ROWNUM.
> So for all those in a simliar situation try something like this:
>
> SELECT * FROM mytable WHERE rownum <= 500
>
>
> -Michael
No, it doesn't. Oracle keeps track of records/rows internally using ROWID, not ROWNUM. ROWNUM is a convenient way to number the records in a result set, nothing more. The first record meeting the select criteria is given ROWNUM 1, and every subsequent record meeting that same criteria increases ROWNUM. It is entirely possible to NOT return a record with a ROWNUM of 1, even when rows would otherwise be returned:
select a from b where c=d and rownum = 30;
will return nothing since you are looking for ROWNUM 30 and, by ancient mathematical principles 1 does NOT equal 30. Since the result criteria cannot be met no ROWNUM of 1 is ever assigned. You, therefore, have nothing returned even if there are 1000 records in table b where c = d.
ROWNUM is for result sets ONLY in Oracle. If you want absolute, concrete values to reference rows in Oracle (outside of a primary key) use ROWID.
David Fitzjarrell Received on Wed Aug 18 2004 - 08:17:11 CDT