Date: Tue, 2 May 2006 19:16:02 -0500
From: Bruno Wolff III <bruno@wolff.to>
To: Volkan YAZICI <yazicivo@ttnet.net.tr>
Cc: Everton =?iso-8859-1?Q?Lu=EDs?= Berz <everton.berz@gmail.com>,
	pgsql-sql@postgresql.org
Subject: Re: Sorting aggregate column contents
Message-ID: <20060503001602.GC31652@wolff.to>
Mail-Followup-To: Bruno Wolff III <bruno@wolff.to>,
	Volkan YAZICI <yazicivo@ttnet.net.tr>,
	Everton =?iso-8859-1?Q?Lu=EDs?= Berz <everton.berz@gmail.com>,
	pgsql-sql@postgresql.org
References: <4457C86F.8040303@gmail.com> <20060502211340.GA188@alamut>
Mime-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <20060502211340.GA188@alamut>
User-Agent: Mutt/1.4.2.1i

On Wed, May 03, 2006 at 00:13:40 +0300,
  Volkan YAZICI <yazicivo@ttnet.net.tr> wrote:
> On May 02 06:00, Everton Luís Berz wrote:
> > Is it possible to sort the content of an aggregate text column?
> > 
> > Query:
> > select s.name, ag_concat(c.name) from state s
> > inner join city c on (c.idstate = s.idstate)
> > group by s.name
> > order by s.name;
> 
> IMHO, you can receive results ordered by using a subselect:
> 
> SELECT T.s_name, ag_concat(T.c_name)
>   FROM (SELECT s.name, c.name
>           FROM state AS s
>           INNER JOIN city AS c ON (c.idstate = s.idstate)
>           ORDER BY s.name, c.name) AS T (s_name, c_name)
>   GROUP BY T.s_name;

Note that this is nonstandard, but is an intentional (but I am not sure if
it's documented) feature of Postgres. There can be some similar situations
where you need to use OFFSET 0 to prevent optimizations that will break the
ordering.