Re: get x number of records from the table

"Rafia Tapia" <rafiat_at_bellatlantic.net> wrote in message news:<LiLn7.7152$tL2.661683_at_news1.rdc1.nj.home.com>...
> Hi
> I have the following problem and any help will be appreciated. I am writing
> an asp application with oracle 8.0 as the backend. I have a table
> with million records. now i have to show 30 records per page. in other words
> the first web page shows the record form 1-30 then when the user clicks next
> the second page shows 31-60 records and so on. My question is how do i tell
> the oracle to send me the first 30 records or the 30-60 records and so forth
>
> Rafia

You would use rownum, but I suppose you want to show those records in alphabetical order. The problem is you cannot use a select object from table
 where rownum <10
 order by 1;
because this statement return an unwanted answer, it takes the first 10 rows and then orders them. You must use a select from select statement. Another issue is you could use only fragments of code like rownum < 10 or rownum <=10, but you cannot use rownum >10 or rownum >=10 or rownum between 10 and 20. To pass obstacle this you could use minus operator.
 So, let see, if you have a table named tab with a varchar2 column named val
(created for example with "create table tab(val varchar2(100));", and some records inserted in the table tab, To get the records between 30-60, you may issue the next statement:

select val from
  (select val from tab order by val)
where rownum <30
minus
select val from
 (select val from tab order by 1)
where rownum <=60
/

For the rest, I know jsp, not asp, but I think a select is easy to do in asp. Good luck, I hope this is what you wanted to know.

Ciprian Received on Sat Sep 15 2001 - 21:29:21 CEST