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: Retrieve specified number of rows

Re: Retrieve specified number of rows

From: Tom Best <tom.best_at_bentley.com>
Date: 2000/08/01
Message-ID: <8m6p9c$dld$1@news.bentley.com>#1/1

Sorry, but it DOES work. Try it. The reason it works (and the reason your explanation is wrong) is that rownum is selected inside the subquery, which takes on all the values possible for all the rows in the table, and then you select from THAT,. and apply the where clause to THAT, which yields the desired rownums.

See this:

SQLWKS> select * from y

     2>
I


         1
         2
         3
         4
         5
         6
         7

7 rows selected.
SQLWKS> select * from (select rownum r, i from y order by i)
     2> where r between 3 and 5
     3>
R          I
---------- ----------
         3          3
         4          4
         5          5

3 rows selected.
SQLWKS> select * from (select rownum r, i from y order by i)
     2> where r between 2 and 3
     3>
R          I
---------- ----------
         2          2
         3          3

2 rows selected.

HTH,
Tom Best

Tony Simopoulos <karkalis_at_earthling.net> wrote in message news:osodosk9ffs45236i4fh0dmnh769gl4sf0_at_4ax.com...
> On Mon, 31 Jul 2000 15:25:12 -0400, "Tom Best" <tom.best_at_bentley.com>
> wrote:
>
> >You can do this:
> >
> >select * from (select rownum r, cola, colb from mytable order by primkey)
> >where r between 200001 and 400000
> >
> a simple test, shows that this doesn't work. reason .. rownum is
> always sequential from 1 to the number of rows selected. by
> definition, you can't select 200,000 rows, without rownum taking on
> the values 1 to 200,000.
>
> a better way (the only way??) is to use database cursors.
>
> what utility or programming language are you using for access?
>
> tonys
> >HTH,
> >Tom Best
> >
> >Calvin King <cking_at_sandia.gov> wrote in message
> >news:3985CBE9.764B29CD_at_sandia.gov...
> >> I am working with a table that has about 25 million rows.
> >>
> >> I would like to retrieve the first 200,000 rows, then the second
 200,000
> >> rows, and so forth... sorted by the primary key.
> >>
> >> Any SQL suggestions/solutions would be much appreciated.
> >>
> >> Calvin
> >
>
Received on Tue Aug 01 2000 - 00:00:00 CDT

Original text of this message

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