Re: Select First 500 Rows of a Table

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 18 Aug 2004 15:24:09 GMT
Message-ID: <slrnci6t4m.3tc.rene.nyffenegger@zhnt60m34.netarchitects.com>

> In article <91721cf.0408171304.50618189_at_posting.google.com>, Michael wrote:

>> Hello,
>> 
>> In Oracle 8.17 from SQLPLus how can I SELECT the first 500 rows of a
>> table?  I am versed in creating (filtering out) data using a WHERE
>> clause - this is different than that.
>> 
>> I ideally would like to SELECT the first 500 records of a table then
>> the NEXT 500 and so forth.
>> 
>> Thanks for your advice,
>> 
>> Michael

>
> First, first rows is a bit vague. There needs to be a sorting criteria.
>
> Something like this should work:
>
> select
> x, y, z
> from (
> select x,y,z, ROWNUM r
> from my precious_table
> )
> where r between 1 and 500;
>
>
> See also http://www.adp-gmbh.ch/ora/sql/examples/first_rows.html

Sorry, I forgot the most important thing: the order by clause.

select
  x, y, z
from (
  select x,y,z, ROWNUM r
  from my precious_table
  order by foo
)
where r between 1 and 500;

Now, it is also possible to select 501 trhough 1000:

select
  x, y, z
from (
  select x,y,z, ROWNUM r
  from my precious_table
  order by foo
)
where r between 501 and 1000;

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch/

Received on Wed Aug 18 2004 - 10:24:09 CDT