Re: Aggregates, group, and order by

Joe Conway <mail(at)joeconway(dot)com> writes:
> Michael Glaesemann wrote:
>> I'm trying to concatenate strings in variable orders using a custom
>> aggregate.

> Just use a subselect -- you're looking for this, correct?

> regression=# select bar_id, array_accum(foo_value) from (select * from
> ordered_foo order by foo_pos) as ss group by bar_id order by bar_id;

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.

regards, tom lane