Re: Next Pages!!!!!??????

But you can do:

select ....
from (
select t1.*,rownum row_num from t1 where rownum <=50) where row_num > 25;

Note, if you're using a cursor and you do a select * from t1 then you can just fetch the first 25 rows. Leave the cursor open and fetch the next 25 rows when you wish. The optimser will always return the rows as they are read so in a simple table scan it will return them as they are fetched, not when the sql statement is executed.

You just need to watch out for sorts and more complex type joins. In this case the optimser might need to process all the rows before in can start passing back any rows. The only way to make sure is to use explain.

Andrew Tacchi wrote:

> The second query will not work, you will just get no rows selected.
>
> It is only possible to use < and <= with rownum as this is only calculated
> after the query has executed. Thus =, > and >= do not work.
>
> A way round it is the following
>
> select c1, c2, ..
> from t1
> where rownum <= 50
> minus
> select c1, c2, ...
> from t1
> where rownum <= 25
>
> Also remember that using the order by will not bring back the first 25 rows
> by the ordered column as rownum is calculated before the order by, thus
> order by has no effect on the rownum.
>
> E.G.
>
> select rownum, c1, c2
> from t1
>
> 1, a1, c2
> 2, b1, b2
> 3, c1, d2
> 4, d1, a2
>
> select rownum, c1, c2
> from t1
> order by c2
>
> 4, d1, a2
> 2, b1, b2
> 1, a1, c2
> 3, c1, s2
>
> Tacchi
>
> Mike Rose wrote in message <4XkF2.4805$573.2266_at_news.rdc1.md.home.com>...
> >You can do the following:
> >
> >1st 25 rows
> >Select C1, C2, Cn
> >From Table Where ROWNUM <= 25
> >
> >rows 26 ->50
> >Select C1, C2, Cn
> >From Table
> >Where ROWNUM > 25 AND
> > ROWNUM <=50
> >
> >The "<=" is used since we're not sure what the last ROWNUM value is. The
> >actual rows retrieved by this method can vary if the underlying table isn't
> >static.
> >
> >Mike Rose
> >
> >Chenping Tsou wrote in message <36E5C2E9.B798E4AB_at_iii.org.tw>...
> >>Is there a way that I could fetch the first 25 records of a infinite
> >>sized table, then issue a command to fetch the next 25 records through
> >>SQL command?
> >>Basically I want to display data usgin Java Servlet to a browser
> >>interface. The first procedure
> >>would be to get the first 25 records, then if the user pressed next
> >>page, it will display the next 25 records. I don't want to load the
> >>whole table as this would be very time consuming. I only want to load
> >>the records that I am displaying.
> >>
> >>Thanks, Ping.
> >>
> >
Received on Wed Mar 10 1999 - 00:00:00 CET