Re: getting rownum 500000 - 500050 from a database

Just a further thought - partly sparked by Wim Mortlemans question - if you want to retrieve between a certain number of rows and then allow the user to go backwards or forwards a set at a time, eg web browser query, then you could modify the query as follows, so that the performance improves with each new set retreived as the set gets potentially smaller each time.

select *
from emp
where empno in ( select empno

                 from (select empno, rownum line_no 
                       from ( select empno from emp
                              where (    (&mode = 'START'
                                          and  empno > -1
                                          ) 
                                      or ( &mode = 'FORWARD'
                                           and  empno > &last_empno
                                         )
                                      or ( &mode = 'BACK'
                                           and   empno < &first_empno
                                         )
                                     )
                              group by empno
                            )
                       )
                 where line_no between &bottom_limit and &top_limit
                )

Rod Corderey

Lane Associates
Lane_Associates_at_Compuserve.com
http://www.Lane-Associates.com  

Marc Parkinson wrote:
>
> I need to get row numbers 500000 through 500050 from a database.
> I also need to do an order by clause before I retreive the data.
>
> Any ideas on how to do this other then reading the fir 500000 rows of
> data??
>
> thanks
>
> Marc Parkinson
> marcpark_at_uswest.net
Received on Wed Nov 17 1999 - 11:55:02 CST