Re: Second highest value

I agree that will return the correct two values, but performance will probably be not great for large recordsets. MIN/MAX even on an index is ranked 13 in the optimizer access paths. I find it difficult to believe anyway that 8.0.x doesn't have 'TOP'. Why is this?

In article <newscache$nnn7gf$zoo$1_at_njord.rim.net>,   "Mike Heisz" <mheisz_at_rim.net> wrote:
> This should work and solve that problem:
> select max(invoicedate)
> from myview
> union
> select max(invoicedate)
> from myview
> where invoicedate < (select max(invoicedate)
> from myview);
>
> Mike
>
> <morris_minor_at_my-deja.com> wrote in message
> news:7on139$mh2$1_at_nnrp1.deja.com...
> > In article <37AC2940.17BE63EC_at_Unforgettable.com>,
> > BluesSax_at_Unforgettable.com wrote:
> > > select max(invoicedate)
> > > from myview
> > > union
> > > select invoicedate
> > > from myview
> > > where invoicedate < (select max(invoicedate)
> > > from myview)
> > > and rownum = 1;
> > >
> > Will this not just select, for the second value, simply the first
> > record to come out of the database? Invoice date in this case is not
> > guaranteed to be retrieved in date order, so it is not necessarily
the
> > second highest.
> >
> >
> > Sent via Deja.com http://www.deja.com/
> > Share what you know. Learn what you don't.
>
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Tue Aug 10 1999 - 04:18:06 CDT