Re: how to limit the output?

A copy of this was sent to kev <kevin.porter_at_fast.no> (if that email address didn't require changing) On Wed, 27 Oct 1999 13:11:10 +0100, you wrote:

>Steve McDaniels wrote:
>
>> select * from
>> ( select field1, field2, ... from table_T where <blah blah> order by fieldn
>> , etc)
>> where rownum <= 3
>> /
>>
>
>Have you tried this?
>
>Here's what happens in 8.0.5 (because an ORDER BY in a view [subquery] is not
>allowed):
>
>SVRMGR> select * from ( select id, title from story order by id ) where rownum
>< 4;
>select * from ( select id, title from story order by id ) where rownum < 4
> *
>ORA-00907: missing right parenthesis
>
>Does this work in any version above 8.0.5?
>

Yes, this syntax is supported in Oracle8i, release 8.1 and up.

>- Kev
>
>>
>> kev <kevin.porter_at_fast.no> wrote in message
>> news:3815ACEB.E5D50D25_at_fast.no...
>> > Brian Peasland wrote:
>> >
>> > > Try:
>> > >
>> > > SELECT * FROM table WHERE ROWNUM <= 3;
>> > >
>> >
>> > On 8.0.5 this doesn't work if you have an ORDER BY clause, because Oracle
>> > works out the rownum before it does the ORDER BY.
>> >
>> > I've heard that that behaviour has changed in 8.1.5. Can anyone confirm
>> > this?
>> > I would _really_ like to use rownums and ORDER BYs.
>> >
>> > thanks,
>> >
>> > - Kev
>> >
>> >

--
See http://osi.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 Oct 27 1999 - 07:55:54 CDT