Re: Aggregates, group, and order by - Mailing list pgsql-general

From Joe Conway
Subject Re: Aggregates, group, and order by
Date
Msg-id 436F19BF.3050401@joeconway.com
Whole thread Raw
In response to Aggregates, group, and order by  (Michael Glaesemann <grzm@myrealbox.com>)
Responses Re: Aggregates, group, and order by
List pgsql-general
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

pgsql-general by date:

Previous
From: David Fetter
Date:
Subject: Re: Aggregates, group, and order by
Next
From: Michael Glaesemann
Date:
Subject: Re: Aggregates, group, and order by