Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id B1B5F634820 for ; Mon, 14 Sep 2009 11:31:51 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 14110-05 for ; Mon, 14 Sep 2009 14:31:31 +0000 (UTC) Received: from news.hub.org (news.hub.org [200.46.204.72]) by mail.postgresql.org (Postfix) with ESMTP id CFEC76345A5 for ; Mon, 14 Sep 2009 11:31:40 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.14.3/8.14.3) with ESMTP id n8EEVXq5028901 for ; Mon, 14 Sep 2009 11:31:33 -0300 (ADT) (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.14.3/8.14.3/Submit) id n8EEPSrl027128 for pgsql-sql@postgresql.org; Mon, 14 Sep 2009 11:25:28 -0300 (ADT) (envelope-from news) From: wstrzalka X-Newsgroups: pgsql.sql Subject: ordered by join? ranked aggregate? how to? Date: Mon, 14 Sep 2009 07:25:33 -0700 (PDT) Organization: http://groups.google.com Lines: 27 Message-ID: Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 X-Complaints-To: groups-abuse@google.com Complaints-To: groups-abuse@google.com Injection-Info: l34g2000vba.googlegroups.com; posting-host=89.174.79.62; posting-account=9WkiLgoAAAAu9mBUBORgx8ddEnvDVjcP User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 6.0; pl; rv:1.9.0.14) Gecko/2009082707 Firefox/3.0.14 (.NET CLR 3.5.30729), gzip(gfe), gzip(gfe) To: pgsql-sql@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=-10 required=5 tests=BAYES_50=0.001 X-Spam-Level: X-Archive-Number: 200909/62 X-Sequence-Number: 33127 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 :/