DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:user-agent:x-accept-language:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding;
	b=rMDKIxh0BwpPcf25+BYSKBUWjDOE7q5QCRP4PxsikDJZN2JR6bIQKKNlMm8WSKDWVpCrOoZr9MIs2JsYLriezs6vMcZ9m89m1vx4HHXhAVev340YuFaRkNMsOufPKiQV1eH7E3i8UM5QmebWvqY3PMM9zu8gMk8WYbu2UyguFLw=
Message-ID: <4458C6ED.2060002@gmail.com>
Date: Wed, 03 May 2006 12:06:21 -0300
From: =?ISO-8859-1?Q?Everton_Lu=EDs_Berz?= <everton.berz@gmail.com>
User-Agent: Mozilla Thunderbird 1.0 (X11/20041206)
MIME-Version: 1.0
To: "Ben K." <bkim@coe.tamu.edu>
CC: Volkan YAZICI <yazicivo@ttnet.net.tr>,  pgsql-sql@postgresql.org
Subject: Re: Sorting aggregate column contents
References: <4457C86F.8040303@gmail.com> <20060502211340.GA188@alamut>
	<4457F83C.7020505@gmail.com>
	<Pine.GSO.4.64.0605022222030.18622@coe.tamu.edu>
In-Reply-To: <Pine.GSO.4.64.0605022222030.18622@coe.tamu.edu>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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
>