Re: how to limit the output?

Thomas Kyte wrote:

> 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.

That's great, but before I ask our admin guys to uninstall 8.0.5 and install 8i instead (which would be a huge bind), seeing as you're a guru, could you tell me definitively what is the best way to emulate this behaviour in 8.0.5 ?

thanks,

• Kev

>
>
> >- 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 - 08:31:50 CDT