> 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