Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 9BF501337B47 for ; Thu, 23 Sep 2010 07:01:49 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 37476-06 for ; Thu, 23 Sep 2010 10:01:43 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from jupiter.marktest.pt (delta.marktest.pt [80.251.174.116]) by mail.postgresql.org (Postfix) with ESMTP id 34BD0133729E for ; Thu, 23 Sep 2010 07:01:40 -0300 (ADT) Received: from Proteu (proteu.marktest.pt [10.61.90.54]) by jupiter.marktest.pt with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2653.13) id SBPT6WM0; Thu, 23 Sep 2010 11:01:39 +0100 Message-ID: From: "Oliveiros d'Azevedo Cristina" To: "Adrian Johnson" , References: Subject: Re: unique fields Date: Thu, 23 Sep 2010 11:01:39 +0100 MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.5512 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.428 tagged_above=-5 required=5 tests=BAYES_20=-0.001, STOX_REPLY_TYPE=0.439, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201009/85 X-Sequence-Number: 34616 Howdy, Adrian Dunno if this is exactly what you want SELECT * FROM ( SELECT chr,cfrom,cto,count(*) as numberOfDuplicates FROM t_fairly_large_table GROUP BY chr,cfrom,cto ) x NATURAL JOIN t_fairly_large_table y WHERE numberOfDuplicates > 1 The idea of this (untested) query is to produce something like chr| cfrom | cto | numberOfDuplicates| sample_id c2 19 20 3 1 c2 19 20 3 2 c2 19 20 3 3 c5 10 11 2 1 c5 10 11 2 3 Can this be what you need? Best, Oliver ----- Original Message ----- From: "Adrian Johnson" To: Sent: Thursday, September 23, 2010 4:30 AM Subject: [SQL] unique fields > hi: > > I have a fairly large table. > > sample_id | chr | cfrom | cto | > ------------------------------------------- > 1 c2 19 20 > 2 c2 19 20 > 3 c2 19 20 > 1 c5 10 11 > 3 c5 10 11 > > > (25,000 rows) > > I want to find out how many duplications are there for chr, cfrom and cto > > a. c2,19,20 are common to samples 1,2 and 3. > > since there will be many instances like that, do I have to loop over > entire rows and find common chr, cfrom and c2 and ouput with > sample_id. > how can I do that. > > thanks > adrian > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql