From: Ben K. <bkim@coe.tamu.edu>
To: Everton Luís Berz <everton.berz@gmail.com>
Cc: Volkan YAZICI <yazicivo@ttnet.net.tr>
Cc: pgsql-sql@postgresql.org
Subject: Re: Sorting aggregate column contents
Date: Tue, 2 May 2006 23:17:47 -0500 (CDT)
Message-ID: <Pine.GSO.4.64.0605022222030.18622@coe.tamu.edu> (raw)
In-Reply-To: <4457F83C.7020505@gmail.com>
References: <4457C86F.8040303@gmail.com>
<20060502211340.GA188@alamut>
<4457F83C.7020505@gmail.com>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bkim@coe.tamu.edu, everton.berz@gmail.com, yazicivo@ttnet.net.tr
Subject: Re: Sorting aggregate column contents
In-Reply-To: <Pine.GSO.4.64.0605022222030.18622@coe.tamu.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox