From: Everton Luís Berz <everton.berz@gmail.com>
To: Ben K. <bkim@coe.tamu.edu>
Cc: Volkan YAZICI <yazicivo@ttnet.net.tr>
Cc: pgsql-sql@postgresql.org
Subject: Re: Sorting aggregate column contents
Date: Wed, 03 May 2006 12:06:21 -0300
Message-ID: <4458C6ED.2060002@gmail.com> (raw)
In-Reply-To: <Pine.GSO.4.64.0605022222030.18622@coe.tamu.edu>
References: <4457C86F.8040303@gmail.com>
<20060502211340.GA188@alamut>
<4457F83C.7020505@gmail.com>
<Pine.GSO.4.64.0605022222030.18622@coe.tamu.edu>
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
>
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: everton.berz@gmail.com, bkim@coe.tamu.edu, yazicivo@ttnet.net.tr
Subject: Re: Sorting aggregate column contents
In-Reply-To: <4458C6ED.2060002@gmail.com>
* 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