Re: Top N in Oracle 8.0.5

True. Oracle does state that distinct is not guaranteed to return ordered result sets.
A case would be when you are using the PQO. At least in pre-8i, for non-PQO, distinct does return an ordered resultset since Oracle does a *sort* to find the distinct rows.

Having said that .. :) I agree completely with Niall. I should know better than suggesting a solution which might fail in later Oracle releases or in case of a configuration change...

Niall, I think the INDEX hint has the exact same pitfall with PQO. It too does not
guarantee an ordered resultset (correct me if wrong?).

Anurag

"Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote in message news:3d221b5e$0$8506$cc9e4d1f_at_news.dial.pipex.com...
> "Anurag Varma" <avdbi_at_hotmail.com> wrote in message
> news:PMjU8.4622$68.82651_at_news4.srv.hcvlny.cv.net...
> > I might be wrong ... and cannot test this .. but I remember using
distinct
> > clause to sort the subquery results.
>
> Distinct doesn't guarantee an ordered resultset ONLY order by does. I'll
> agree that generally you'll get one but if you don't its just tough and
that
> way unresolvable bugs lie.
>
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
>
>
>
>
Received on Tue Jul 02 2002 - 17:48:04 CDT