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

Chris Fischer wrote:
>
> pat palguta <pat.palguta_at_daytonoh.ncr.com> wrote:
>
> >The GROUP BY clause will NOT order your results. It simply
> >"groups" like information into a single row summary result.
> >Thus the name.
>
> So then, how do you suppose Oracle knows when to stop summarizing for
> one group and start summarizing for the next group? It has to sort
> the rows by the GROUP BY columns first.
>

I don't think you can guarantee that the results of the ORDER BY will be in order. On a system I worked on several years ago I noticed that the output of several summary reports (that used GROUP BY) were no longer in the expected order after I moved from rule-based to cost-based optimization. Adding an ORDER BY to the queries fixed the problem. The cost-based optimizer must have chosen a different method to perform the aggregation. Sorry, but I didn't keep the execution plans.

-- 
Roger Harris
Oracle DBA
GE Credit Corp
Danbury, CT