Date: Tue, 15 Sep 2009 09:49:57 +0200
From: "A. Kretschmer" <andreas.kretschmer@schollglas.com>
To: pgsql-sql@postgresql.org
Subject: Re: ordered by join? ranked aggregate? how to?
Message-ID: <20090915074957.GA8046@a-kretschmer.de>
References: 
 <d7867e26-d5de-4c48-bfac-d6f266b47ec6@l34g2000vba.googlegroups.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <d7867e26-d5de-4c48-bfac-d6f266b47ec6@l34g2000vba.googlegroups.com>
User-Agent: Mutt/1.5.9i

In response to wstrzalka :
> What I need is to join 2 tables
> 
> CREATE TABLE master(
>     id INT4
> );
> 
> 
> CREATE TABLE slave (
>     master_id INT4,
>     rank INT4,
>     value TEXT);
> 
> 
> What I need is to make the query:
> 
> SELECT m.id, array_agg(s.value) AS my_problematic_array
> FROM master AS m LEFT JOIN slave AS s ON (m.id = s.master_id)
> GROUP BY m.id;
> 
> return the 'my_problematic_array' in order specified by slave.rank
> 
> As you probably can guest I don't have any idea know how to do it :/

test=*# select * from master;
 id
----
  1
  2
(2 rows)

test=*# select * from slave;
 master_id | rank | value
-----------+------+-------
         1 |    5 | 5
         1 |    3 | 3
         1 |    7 | 7
(3 rows)

test=*# 
select id, array_agg(unnest) from (
  select id, unnest(my_problematic_array) from (
    SELECT m.id, array_agg(s.value) AS my_problematic_array FROM master AS m LEFT JOIN slave AS s ON (m.id = s.master_id) GROUP BY m.id
  ) foo order by 1,2
) bar group by 1;
 id | array_agg
----+-----------
  1 | {3,5,7}
  2 | {NULL}
(2 rows)


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)