Thread: PostgreSQL group sort

use these sqls:

CREATE TABLE big(id serial, name text);
CREATE TABLE sm(id serial, big_id integer, name text);

INSERT into big (id, name) VALUES (1, 'big1');
INSERT into big (id, name) VALUES (2, 'big2');

INSERT into sm(id, big_id, name)VALUES (2, 1, 'sm1');
INSERT into sm(id, big_id, name)VALUES (1, 1, 'sm2');
INSERT into sm(id, big_id, name)VALUES (3, 1, 'sm3');
INSERT into sm(id, big_id, name)VALUES (6, 2, 'sm4');
INSERT into sm(id, big_id, name)VALUES (4, 2, 'sm5');
INSERT into sm(id, big_id, name)VALUES (4, 2, 'sm6');


--------------------------------

run :
SELECT b.id, array_accum(s.id), array_accum(s.name)from big b, sm s where b.id = s.big_id group by b.id;
(ps: array_accum is  aggregate in http://www.postgresql.org/docs/9.0/static/xaggr.html)

id | array_accum |  array_accum 
----+-------------+---------------
  1 | {2,1,3}     | {sm1,sm2,sm3}
  2 | {6,4,4}     | {sm4,sm5,sm6}
(2 rows)

the excepted result:
 id | array_accum |  array_accum 
----+-------------+---------------
  1 | {1,2,3}     | {sm1,sm2,sm3}
  2 | {4,5,6}     | {sm4,sm5,sm6}
(2 rows)


It is a group sort ?

On Feb 25, 2011, at 11:45 AM, zab08 wrote:

> run :
> SELECT b.id, array_accum(s.id), array_accum(s.name)from big b, sm s where b.id = s.big_id group by b.id;
> (ps: array_accum is  aggregate in http://www.postgresql.org/docs/9.0/static/xaggr.html)
>
> id | array_accum |  array_accum
> ----+-------------+---------------
>   1 | {2,1,3}     | {sm1,sm2,sm3}
>   2 | {6,4,4}     | {sm4,sm5,sm6}
> (2 rows)
>
> the excepted result:
>  id | array_accum |  array_accum
> ----+-------------+---------------
>   1 | {1,2,3}     | {sm1,sm2,sm3}
>   2 | {4,5,6}     | {sm4,sm5,sm6}


Try with intarray Module:
http://www.postgresql.org/docs/8.4/static/intarray.html

Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
Cell: +91-932-568-2279
vibhor.kumar@enterprisedb.com
Blog:http://vibhork.blogspot.com