Re: how to limit the output?

From: kev <kevin.porter_at_fast.no>
Date: Wed, 27 Oct 1999 13:11:10 +0100
Message-ID: <3816EBDE.E1B5DAAB@fast.no>

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?

• 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
> >
> >
Received on Wed Oct 27 1999 - 07:11:10 CDT