Date: Tue, 15 Sep 2009 09:56:05 +0200
From: "A. Kretschmer" <andreas.kretschmer@schollglas.com>
To: pgsql-sql@postgresql.org
Subject: Re: ordered by join? ranked aggregate? how to?
Message-ID: <20090915075605.GB8046@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;

Faster solution (compared to my other email):

test=# select id, array_agg(value) from (SELECT m.id, s.value FROM
master AS m LEFT JOIN slave AS s ON (m.id = s.master_id) order by 1,2)
foo 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)