Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: Retrieve specified number of rows
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
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
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
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
![]() |
![]() |