Re: paginated resultset for web display

select * from
 (select rownum n,fname,... from
  emp
  where lname like :name || '%'
  and rownum <= :stop
  order by <<something>>
 ) where n > :start

Always use bind parameters where possible.

Depending on the range you're looking for this may be inefficent; I'm not sure if Oracle needs to build a temporary table for this type of query. If so and it's possible for there to be thousands of records and for the user to jump to arbitrary pages then this may be too memory and temporary tablespace intensive to be feasible.

Peter Rexer <prexer_at_my-deja.com> writes:

> I'm trying to set up a query to bring back rows 1-50, then 51-100 from
> a web application. Is there an easy way to have Oracle do this for me,
> or do I need to bring back the resultset and store it in my web
> server's cache?
>
> Ideally I'm looking for a way to do a query something like this:
>
> Select fname, lname from emp where lname like 'Smith%'
> and rownum >50
> and rownum <101
>
>
> Peter Rexer
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.

--
greg Received on Wed Oct 06 1999 - 23:44:32 CDT