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 00E2611F6657 for ; Wed, 29 Mar 2006 09:40:19 -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 34264-08 for ; Wed, 29 Mar 2006 09:40:18 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.logix-tt.com (unknown [212.211.145.186]) by postgresql.org (Postfix) with ESMTP id 4085511F6654 for ; Wed, 29 Mar 2006 09:40:16 -0400 (AST) Received: from kingfisher.intern.logix-tt.com (Ta2ae.t.pppool.de [89.55.162.174]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by mail.logix-tt.com (Postfix) with ESMTP id E8D43657C3 for ; Wed, 29 Mar 2006 15:40:56 +0200 (CEST) Received: from [127.0.0.1] (localhost [127.0.0.1]) by kingfisher.intern.logix-tt.com (Postfix) with ESMTP id C743A18530649 for ; Wed, 29 Mar 2006 15:40:27 +0200 (CEST) Message-ID: <442A8E4B.7050106@logix-tt.com> Date: Wed, 29 Mar 2006 15:40:27 +0200 From: Markus Schaber Organization: Logical Tracking and Tracing International AG, Switzerland User-Agent: Debian Thunderbird 1.0.7 (X11/20051017) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Ugly group by problem X-Enigmail-Version: 0.93.0.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.113 required=5 tests=[AWL=0.113] X-Spam-Score: 0.113 X-Spam-Level: X-Archive-Number: 200603/452 X-Sequence-Number: 24798 Hello, I have a table filled from third party that basically has the following structure: link_id | feat_id | other | columns... --------+---------+-------+----------- 1 | 2 | ... 2 | 5 | ... 2 | 23 | ... 3 | 5 | ... 3 | 23 | some | data 3 | 23 | other | data 5 | 23 | ... 9 | 23 | ... This structure is fixed, and we can't change it, but we can create We have about 37 million different link_ids, and 35 million feat_ids. There are feat_ids that appear at several thousand link_ids, but a link_id does not have more than a douzen feat_ids. Now I need to group together all link_ids that have the same set of feat_ids. In the example above, the sets would be (1), (2,3) and (5,9), and the other columns would be run through some aggregate functions. Currently, this is done via an external JAVA application, but I'm looking for a way to express this via sql / plpgsql to ease deployment. I could imagine some ugly code using ARRAY (not tried yet), but how would you pack this problem? It seems that I'm just stuck in my thoughts and miss the beauty way to solve it. Thanks, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org