Re: Aggregates, group, and order by

On Nov 7, 2005, at 23:24 , Tom Lane wrote:

> Strictly speaking, you need this:
>
>     select bar_id, array_accum(foo_value) from
>     (select * from ordered_foo order by bar_id, foo_pos) as ss
>     group by bar_id order by bar_id;
>
> ie, sort the subselect by the grouping key of the outer query, then
> by the thing that should control the aggregation order within groups.
>
> The way Joe shows will work only if the planner chooses to use a hash
> aggregate plan.  If it chooses a sort/uniq aggregation plan, the re-
> sort
> will destroy the sort order of the sub-select's output.

Thanks, Tom. That fixed the problem in the production version. Is the
ability to do this an artifact of the sorting or defined by the SQL?
I.e., can I expect this to work even if the plan changes? I'm
guessing not, but that's just a guess.

Michael Glaesemann
grzm myrealbox com