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

It all depends on the size of your table. Consider the following,

select *
from emp
where empno in ( select empno

                 from (select empno, rownum line_no 
                       from ( select empno
                              from emp
                              group by empno
                            )
                       )
                 where line_no between &bottom_limit and &top_limit
                )

 if &bottom_limit is 3 and &top_limit is 6, then the result of this will be to select all rows from emp from the third empno to the sixth in empno order, regardless of the values of empno.

But a big but, if your table was large, say 1M plus rows, then the inner-most query could take some time even allowing for the fact that it is only processing the primary index, as of course it handles all of them and not just the ones that you want.

Running the above against a table of 14,000 records with a three column concatenated key on a low spec pentium pc, NT4, 7.3, took approx 6.5 secs to retrieve 12 rows from the 1200th up, most of this time would be absorbed by the redundant activities of the innermost query happily processing the full 14,000.

Given that an alternative is to use an cursor and 'throw away' the rows you don't need up to the first that you do need, then the pluses and minuses are about table size and required set size as a proportion of table size and how far through the table you need to start and possibly the degree of data fragmentation in the table.

To travel backwards and forwards from the current point in the data set, then the following variation can be used, which progressively improves the performance a little as the user traverses a previously retrieved set.