Re: Limit a result list

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 3 Dec 2002 07:54:51 -0800
Message-ID: <asik4b0hfp@drn.newsguy.com>

In article <MPG.1856e8aa74b3ab429896cf_at_news.cis.dfn.de>, Alexander says...
>
>Do it like this:
>
> select * from
> (
> SELECT t.*, ROWNUM rn FROM tablename t WHERE something
> ORDER BY entrydate
> )
> where rn between 30 and 40;
>

well, that won't work -- rownum needs to be assigned AFTER the order by -- you have it assigned before the order by.

better yet, do it like this:

select *

   from ( select a.*, rownum rn

            from ( select * from tablename where something order by whatever )
           where rownum <= 40
        )

 where rn >= 30;

see
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:948366252775 and search for (ctl-f)

simpler

to see why you want two predicates -- not one between.

>It does not work without the subselect, because ROWNUM would never
>exceed 1:
>
>Consider the following select:
>
> SELECT t.*, ROWNUM rn FROM tablename t WHERE something ORDER BY
> WHERE ROWNUM between 30 and 40;
>
>Fetch first row. Rownum between 30 and 40? No, it is 1.
>Fetch second row. Rownum between 30 and 40? No, it is STILL 1 (because
>this would be the FIRST row to be fetched).
>Fetch third row. Rownum between 30 and 40? No, it is STILL 1.
>
>Thus, there would never be a row returned. You have to select the rownum
>first and THEN subquery and "where" it.
>
>hth
>Alex
>
>
>
>oracle_at_banholzer.de tipperte...
>> I have the following problem:
>>
>> How can I get the results (for example 30 - 39) from a select statement?
>> The result list should by ordered by a date.
>>
>> With MySQL I did it like :
>>
>> SELECT * FROM tablename WHERE something ORDER BY entrydate LIMIT 30, 10

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Tue Dec 03 2002 - 09:54:51 CST