Re: Need SQL guru's help: Best query?

> The GROUP BY clause will NOT order your results. It simply
> "groups" like information into a single row summary result.
> Thus the name.
> ==========Chris Fischer, 3/25/96==========
> cdye_at_dnt.dialog.com (Charles Dye (PC)) wrote:
>
> >SELECT model_number, min(model_number)
> >FROM model_table
> >group by model_number
> >order by model_number
> >/
>
> In this case, the ORDER BY clause is redundant and unnecessary as to
> do the GROUP BY oracle will already sort the rows. Adding the order
> by simply causes oracle to sort the rows again.

Probably, if you make a test, you'll see that Oracle does sort the result using only the group by. Even if you test it, you should remember that the reason "can" be that Oracle sorts the rows to do the grouping. However, Oracle is not commited to do it forever. I mean, Oracle can change the algorithim in the feature to achieve better performances. Put that order by and be sure that your application will work in future versions of Oracle. I hope that Oracle does nothing with that order by if they know (the optimizer) that the group by does the sorting. That's not our concern to avoid two sortings... that's Oracle's concern (I hope :-) ).

Regards,

Carlos Netto
cnetto_at_cps.softex.br - Software Design Ltd, Brazil Received on Fri Mar 29 1996 - 00:00:00 CET