that makes more sense -- after i posted i was thinking about that approach,
but haven't had a chance to test it yet
"Connor McDonald" <connor_mcdonald_at_yahoo.com> wrote in message
news:3FA78B0D.6163_at_yahoo.com...
> mcstock wrote:
> >
> > rownum is the pre-sort order of the rows -- rownum = 1 is not always the
> > first row returned:
> >
> > SQL> select dname, deptno
> > 2 from dept
> > 3 order by dname desc;
> > ...
> >
> > DNAME DEPTNO
> > -------------- ----------
> > SALES 30
> > RESEARCH 20
> > OPERATIONS 44
> > ACCOUNTING 10
> >
> > SQL> select dname, deptno
> > 2 from dept
> > 3 where rownum =1
> > 4 order by dname desc;
> > ...
> >
> > DNAME DEPTNO
> > -------------- ----------
> > ACCOUNTING 10
> >
> > however, isn't there another technique or function added in 8i for stuff
> > like first_rows() for top_rows?
> >
> > -- mcs
> >
> > "Connor McDonald" <connor_mcdonald_at_yahoo.com> wrote in message
> > news:3FA6342C.7E0D_at_yahoo.com...
> > >
> > > The too_many_rows is a dead issue.
> > >
> > > If you have a cursor that may return more than 1 row and you code
> > > open/fetch/close, then you are basically seeing that you are
interested
> > > in just the first. In which case, the implicit cursor can be
> > >
> > > select .. into ...
> > > where rownum = 1
> > >
> > > and you'll never ever get the too_many_rows.
>
> select .. into ..
> from ( select x
> from y
> order by z )
> where rownum = 1
>
> and you'll find that runs a whole lot faster than
>
> cursor C is select x from y order by x;
>
> open C; fetch C; close C;
>
> because the former can use an Oracle optimization that the latter
> cannot.
>
> (You'll be able to read all about it in my upcoming book - Stored
> Procedures Applied - late 2003 with Apress)
>
> hth
> connor
Received on Tue Nov 04 2003 - 08:45:53 CST
