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
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
