Re: Aggregates, group, and order by

Michael Glaesemann wrote:
> I'm trying to concatenate strings in variable orders using a custom
> aggregate. However, I'm having a difficult time figuring out the SQL  I
> need to use to accomplish this. Here's a test case that shows the  error
> I'm getting.

> select bar_id, array_accum(foo_value)
> from ordered_foo
> group by bar_id
> order by bar_id;
> bar_id |         array_accum
> --------+-----------------------------
>       1 | {delta,alpha,charlie,bravo}
>       2 | {C,B,A,D}
>
>
> The result I'd like to see is
> bar_id |         array_accum
> --------+-----------------------------
>       1 | {alpha,bravo,charlie,delta}
>       2 | {A,B,C,D}

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;
  bar_id |         array_accum
--------+-----------------------------
       1 | {alpha,bravo,charlie,delta}
       2 | {A,B,C,D}
(2 rows)

HTH,

Joe