Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Select First 500 Rows of a Table

Re: Select First 500 Rows of a Table

From: David Fitzjarrell <fitzjarrell_at_cox.net>
Date: 18 Aug 2004 06:17:11 -0700
Message-ID: <9711ade0.0408180517.25b701d4@posting.google.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US