Re: Select * where rownum betweenFirstRec and LastRec

From: Pavel Pavlov <ppo_at_raid.ru>
Date: Wed, 13 Sep 2000 11:16:57 +0600
Message-ID: <8pn2j6$1n4e$1@second.raid.ru>

Work this
select * from (select rownum m, a.* from (

                                                              select *
                                                              from
t_announce
                                                              where
lower(message) like '%saler%'
                                                              order by
announce_date
                                                             ) a
                    ) b

where b.m between FirstRec and LastRec;

>Actually it will never work like that because rownum accounts for the
>current retrieved record and the only valid predicate in this case is
 rownum
>< 21 (or x). So you can restrict the overall number of records retrieved
 but
>you can't be in control of the record pointer positioning using rownum. You
>can do that by creating a cursor and retrieving chunks of 20 records at a
>time using a cursor pointer for example.

Robert

>> I have written a stored procedura that are going to perform a search and
>> return x records. Next time it is going to return the next y records.
>>
>> Before this code I have created a table and filled it with data.
>>
>> Eg. FromRec = 21 and LastRec = 40
>> I have read some articles that tels me thatthis won't work because rownum
>> between x and y, x has to be 1.
Received on Wed Sep 13 2000 - 00:16:57 CDT