Re: How to get fixed number of rows from the result of SELECT

Sun Guonian <sun_at_cnnic.net.cn> wrote in message news:<3D951EE7.A0B0A883_at_cnnic.net.cn>...
> I have a table with about 1000 records, I want to output
> them to the webpage only 10 records per page by CGI.
> when user click next, he will get the next 10 records.
>
> I could do it by select them all, but it cost too many time.
> I wonder if there some better method to do it with SQL.
>
> Sun Guonian

Hi

I dont know mutch about PERL but if you can use all the features in SQL, you can use a nested select:

select * from (

   select *
   from table
   where rownum < cgi_value * 10 + 1
   order by rownum desc )
where rownum < 11;

The cgi_value here is an counter that increases by 1 for each time the user cliks on next.

Im not sitting on an Oracle base now so i have not tested this SQL, but if it dosen't work it should have given you some pointers. Received on Sat Sep 28 2002 - 12:19:32 CDT