Date: Tue, 2 May 2006 23:17:47 -0500 (CDT)
From: "Ben K." <bkim@coe.tamu.edu>
To: =?ISO-8859-1?Q?Everton_Lu=EDs_Berz?= <everton.berz@gmail.com>
cc: Volkan YAZICI <yazicivo@ttnet.net.tr>, pgsql-sql@postgresql.org
Subject: Re: Sorting aggregate column contents
In-Reply-To: <4457F83C.7020505@gmail.com>
Message-ID: <Pine.GSO.4.64.0605022222030.18622@coe.tamu.edu>
References: <4457C86F.8040303@gmail.com> <20060502211340.GA188@alamut>
	<4457F83C.7020505@gmail.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed
Received-SPF: pass (smtp-relay.tamu.edu: 165.91.232.6 is authenticated by a
	trusted mechanism)

> It works fine. But I wouldn't like using subselect's, then if somebody else
> knows about an operator or something like that to put on the aggregator, 
> please tell me.


I think the nature of the f_concat makes it difficult to sort, since it 
simply adds the next value, so if the source table gives value in the 
order of 'a','c','d','b' there's no way to handle them within f_concat 
unless you modify and rearrange the previous result string from within 
f_concat.


So the source table (city) should be sorted. I don't know if this is a 
standard way, but this one seems to do that.


======================================================
select s.name, ag_concat(c.name) from state s inner join (select * from 
city order by name desc) as c on c.idstate=s.idstate group by s.name order by 1;

OR

select s.name, ag_concat(c.name) from state s, (select * from city order 
by name desc) as c where c.idstate = s.idstate group by s.name order by 1;
======================================================


I'm just reordering the source table on the fly. Curiously, if you don't 
have 'desc' you'll get a reverse ordered list. (z,...,a)

I think your needs may also be met without any aggregator as well (there 
may be marginal cases which I haven't thought of, but I assume they can be 
handled if needed)

======================================================
select s.name, array_to_string(array(select name from city where 
idstate = s.idstate order by name),',') from state s;
======================================================

   name |   array_to_string
------+---------------------
   RP   | Gramado,Port Alegre
   SP   | Osasco


* I see normalization issue here but guess it's not important.


Regards,

Ben K.
Developer
http://benix.tamu.edu