Re: Aggregates, group, and order by

On Nov 7, 2005, at 17:40 , Roger Hand wrote:

> On Monday, November 07, 2005 12:12 AM
> Michael Glaesemann wrote:
>>
>> 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}
>
> select bar_id, array_accum(foo_value)
> from
> (SELECT * FROM ordered_foo ORDER BY foo_pos) foo
> group by bar_id
> order by bar_id;
>
> bar_id,array_accum
> 1,{alpha,bravo,charlie,delta}
> 2,{A,B,C,D}

On Nov 7, 2005, at 18:09 , Joe Conway wrote:
>
> 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)

That is very sweet. Is the subquery guaranteed to retain the order?
My understanding is that a table is not necessarily ordered, so the
result of a subquery isn't necessarily going to be ordered either.

I'm having a bit of trouble getting this to work with the non-reduced
case: the array_accum results aren't always ordered as I want. Going
to mess around with it a little more to see if I can find out why.

Thanks again!

Michael Glaesemann
grzm myrealbox com