Re: Aggregates, group, and order by

Michael Glaesemann <grzm(at)myrealbox(dot)com> writes:
> 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.

> 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?

ORDER BY in a sub-select isn't legal at all per the SQL spec, so all of
this is well outside the bounds of the spec. The planner does take
pains to make this work, though, and I can't see a reason why we'd want
to break it in future versions.

regards, tom lane