Re: Sorting aggregate column contents

Thanks a lot the explanation.

I tested all cases and I noticed that reordering the source table (city)
not works on all cases, so I think Postgresql perform different internal
sort to optimize some query's.
I noticed this in other query I performed:

select s.ano,
s.semestre,
dhc.iddisciplinahorariocurriculo,
count(*),
ag_concatenar_com_quebra_de_linha(td.turno) AS turno
from disciplinahorariocurriculo dhc
inner join horariocurriculo hc on (hc.idhorariocurriculo =
dhc.idhorariocurriculo)
inner join semestre s on (s.idsemestre = hc.idsemestre)
inner join (select tdinterno.iddisciplinahorariocurriculo, t.turno from
turnodisciplina tdinterno
inner join turno t on (t.idturno = tdinterno.idturno)
order by tdinterno.iddisciplinahorariocurriculo, t.turno) as
td on (td.iddisciplinahorariocurriculo = dhc.iddisciplinahorariocurriculo)
-- where dhc.iddisciplinahorariocurriculo = 8282
group by 1, 2, 3
having count(*) > 1
order by 1, 2, 3;

ano | semestre | iddisciplinahorariocurriculo | count | turno
...
2004 | 2 | 8282 | 3 | 23, 63, 43
^ ^ ^
...

If I remove the comment in the 'where' line there is the right result:
ano | semestre | iddisciplinahorariocurriculo | count | turno
------+----------+------------------------------+-------+------------
2004 | 2 | 8282 | 3 | 23, 43, 63
^ ^ ^
(1 row)

I didn't know the array_to_string way, I think I will use it. It's safe
and easy.

Regards,
--
Everton

Ben K. escreveu:
>> 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
>