X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id A944F11F6202 for ; Thu, 30 Mar 2006 05:38:40 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 10967-07 for ; Thu, 30 Mar 2006 05:38:39 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from matrix.gatewaynet.com (host2.dynacom.ondsl.gr [62.103.35.210]) by postgresql.org (Postfix) with ESMTP id 0861011F6AB8 for ; Thu, 30 Mar 2006 05:13:38 -0400 (AST) Received: from matrix.gatewaynet.com (localhost.localdomain [127.0.0.1]) by matrix.gatewaynet.com (8.12.8/8.12.8) with ESMTP id k2UAKNOp023306; Thu, 30 Mar 2006 13:20:23 +0300 Received: from localhost (achill@localhost) by matrix.gatewaynet.com (8.12.8/8.12.8/Submit) with ESMTP id k2UAKN4S023302; Thu, 30 Mar 2006 13:20:23 +0300 Date: Thu, 30 Mar 2006 13:20:23 +0300 (EEST) From: Achilleus Mantzios To: Markus Schaber cc: pgsql-sql@postgresql.org Subject: Re: Ugly group by problem In-Reply-To: <442A9D3A.7010605@logix-tt.com> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=ISO-8859-7 Content-Transfer-Encoding: 8BIT X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.116 required=5 tests=[AWL=0.116] X-Spam-Score: 0.116 X-Spam-Level: X-Archive-Number: 200603/461 X-Sequence-Number: 24807 O Markus Schaber έγραψε στις Mar 29, 2006 : > Hi, Achilleus, > > Achilleus Mantzios wrote: > > > foodb=# SELECT qoo.foo2,sum(qoo.foo3) from (SELECT mt.link_id as > > foo,_int_union(array(select mt2.feat_id from markustest mt2 where > > mt2.link_id=mt.link_id order by mt2.feat_id),'{}') as foo2,other::int4 as > > foo3 from markustest mt) as qoo GROUP BY qoo.foo2; > > foo2 | sum > > --------+----- > > {2} | 1 > > {5,23} | 13 > > {23} | 14 > > (3 rows) > > This is much like I intended to do it, but using "select distinct" in > the inner select as I don't have _int_union here, and using a temporary > table to collect the sets of link ids. Just some facts: sort(uniq(int[])) has the same effect as select DISTINCT... ORDER BY DISTINCT. ORDER BY is ofcourse more intuitive and does not need contrib/intarray. In fact , i dont even remember why i didnt write the query with DISTINCT... in the first place :) > > Markus > > > -- -Achilleus