Re: how do you do pagination in a web app with oracle

In article <9gqbbd$85a$1_at_bob.news.rcn.net>, robert.yeh_at_qwest.com says...
> Just wondering how does people do their pagination in their web application
> with Oracle.
> Say my sql returns 1000 rows. My web GUI displays first 50 rows. I have
> page number 1 - 20 showing on my GUI. If user clicks number 11, it executes
> the same SQL but displays records 501-550.
>
> This is my approach:
>
> 1. do a count(*) of the sql to determine how many rows will be returned.
>
> 2. select field1, field2
> from (select field1, field2, rownum record_number
> from my_table
> where ....)
> where record_number between 501 and 550.
>
> So the SQL always return only 50 rows.
>
>
> Any other suggestions?
>
> Thanks
> Bob
>
>
>

According to my knowledge since 8.1.7 you can use order by in an inline- view. Combined view rownum pscolumn, or rank() over function you can create data windowing like :
select * from
  (select u.*,rownum R from (select usr_name,usr_id from usr_data order by usr_name) u)
where r between 0*10+1 and (0+1)*10

This way you dont have to retrieve all data, just the important part.

Best : Leslie Received on Thu Jun 21 2001 - 06:23:57 CDT