Re: SELECT second 25 records

A copy of this was sent to "Jerry Gitomer" <jgitomer_at_hbsrx.com> (if that email address didn't require changing) On Wed, 4 Aug 1999 09:23:38 -0400, you wrote:

>Hi Maiko,
>
> Sorry but SELECT * FROM <table> WHERE ROWNUM BETWEEN <n> AND
><n1> doesn't work when <n> is greater than one. What will work,
>but is slow is:
>

it works in the example given below. it uses an inline view. consider:

SQL> select * from ( select rownum a, all_users.* from all_users )   2 where a between 10 and 15;

         A USERNAME                          USER_ID CREATED
---------- ------------------------------ ---------- ---------
        10 WEB$AOSULLIV                         1795 01-SEP-97
        11 WEB$ASTULBAR                         1796 01-SEP-97
        12 WEB$JCLEVENG                         1797 01-SEP-97
        13 WEB$JMALLEN                          1798 01-SEP-97
        14 WEB$ZOHANIAN                         1799 01-SEP-97
        15 WEB$MSWANN                           1800 01-SEP-97

6 rows selected.

it has similar performance characteristics to the query you supply tho.

>SQL> l
> 1 select seq from jhgsw55 where rownum between 1 and 10
> 2 minus
> 3* select seq from jhgsw55 where rownum between 1 and 5
>SQL> /
>
> SEQ
>---------
> 6
> 7
> 8
> 9
> 10
>
>SQL>
>
>Note I just happen to have a table laying around with a
>sequential number as the primary key.
>
>regards
>Jerry Gitomer
>
>
>Maiko wrote in message <37a7ed7e.9362973_at_news.cistron.nl>...
>>select naam from (
>>select rownum a,naam from ckv_themas)
>>where a between 1 and 25
>>/
>>
>>did this for me on the first 25,
>>
>>just replace
>>where a between 1 and 25 with values 26 and 50 or something
>>
>>On Tue, 3 Aug 1999 16:01:09 -0500, "Khal" <elsawi_at_xsimple.com>
>wrote:
>>
>>>I'm looking for a way to select the second group of 25 matches
>in a select
>>>statement.
>>>
>>>Any Ideas?
>>>
>>>Khal
>>>
>>
>

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Aug 04 1999 - 10:35:38 CDT