Re: Sorting aggregate column contents - Mailing list pgsql-sql

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
>