Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 58AED9FA4CD for ; Fri, 2 Feb 2007 10:13:34 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 01018-02 for ; Fri, 2 Feb 2007 10:13:25 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from interscan.indicator.com (mail.indicator.be [193.121.199.3]) by postgresql.org (Postfix) with ESMTP id 434169FB1DC for ; Fri, 2 Feb 2007 10:13:24 -0400 (AST) Received: from webaccess.indicator.be ([10.100.1.7]) by interscan.indicator.com with InterScan Message Security Suite; Fri, 02 Feb 2007 15:01:39 +0100 Received: from DOMEXT-MTA by webaccess.indicator.bewith Novell_GroupWise; Fri, 02 Feb 2007 15:13:22 +0100 Message-Id: <45C35506.A3DD.0030.0@indicator.be> X-Mailer: Novell GroupWise Internet Agent 7.0.1 Date: Fri, 02 Feb 2007 15:13:11 +0100 From: "Bart Degryse" To: "Ezequias Rodrigues da Rocha" , Subject: Re: Duplicate records References: <55c095e90702020548h4c5ae5b2h405a2a33fb5b950@mail.gmail.com><45C 350D8.A3DD.0030.0@indicator.be><55c095e90702020559t27296667g5a46aca1b538667 9@mail.gmail.com> In-Reply-To: <55c095e90702020559t27296667g5a46aca1b5386679@mail.gmail.com> Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="=__Part4F6BC4E7.1__=" X-imss-version: 2.046 X-imss-result: Passed X-imss-approveListMatch: *@indicator.be X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.172 tagged_above=0 required=5 tests=AWL, BAYES_50, FORGED_RCVD_HELO, HTML_40_50, HTML_MESSAGE, UNPARSEABLE_RELAY X-Spam-Level: X-Archive-Number: 200702/16 X-Sequence-Number: 27350 --=__Part4F6BC4E7.1__= Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit The only table in my query is "mytable". The part (select field1, field2, count(*) from mytable group by field1, field2 having count(*) > 1) counts how many records have the same field1 and field2 and returns a record per combination of field1 and field2 that have more than one (= duplicates) The rest uses that information to filter all fields of all the records from that same table having such a combination of field1 and field2 This version is even a little shorter: select * from mytable A, (select field1, field2 from mytable group by field1, field2 having count(*) > 1) B where A.field1 = B.field1 and A.field2 = B.field2 >>> "Ezequias Rodrigues da Rocha" 2007-02-02 14:59 >>> Thank you but are you talking of two tables ? I intent to check in only one table. Please explain to me. Ezequias 2007/2/2, Bart Degryse < Bart.Degryse@indicator.be>: select * from mytable A, (select field1, field2, count(*) from mytable group by field1, field2 having count(*) > 1) B where A.field1 = B.field1 and A.field2 = B.field2 >>> "Ezequias Rodrigues da Rocha" 2007-02-02 14:48 >>> Hi list, I am making some data minning and would like to know if someone tell me how to retrieve the duplicate records based in one or two fields of my table. Any help would be welcomed. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Atenciosamente (Sincerely) Ezequias Rodrigues da Rocha =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- A pior das democracias ainda é melhor do que a melhor das ditaduras The worst of democracies is still better than the better of dictatorships http://ezequiasrocha.blogspot.com/ -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Atenciosamente (Sincerely) Ezequias Rodrigues da Rocha =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- A pior das democracias ainda é melhor do que a melhor das ditaduras The worst of democracies is still better than the better of dictatorships http://ezequiasrocha.blogspot.com/ --=__Part4F6BC4E7.1__= Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit Content-Description: HTML
The only table in my query is "mytable".
The part (select field1, field2, count(*) from mytable group by field1, field2 having count(*) > 1)
counts how many records have the same field1 and field2 and returns a record per combination of field1 and field2 that have more than one (= duplicates)
 
The rest uses that information to filter all fields of all the records from that same table having such a combination of field1 and field2
This version is even a little shorter:
select *
from mytable A, (select field1, field2 from mytable group by field1, field2 having count(*) > 1) B
where A.field1 = B.field1 and A.field2 = B.field2

>>> "Ezequias Rodrigues da Rocha" <ezequias.rocha@gmail.com> 2007-02-02 14:59 >>>
Thank you but are you talking of two tables ? I intent to check in only one table.

Please explain to me.

Ezequias

2007/2/2, Bart Degryse < Bart.Degryse@indicator.be>:
select *
from mytable A, (select field1, field2, count(*) from mytable group by field1, field2 having count(*) > 1) B
where A.field1 = B.field1 and A.field2 = B.field2

>>> "Ezequias Rodrigues da Rocha" < ezequias.rocha@gmail.com> 2007-02-02 14:48 >>>

Hi list,

I am making some data minning and would like to know if someone tell me how to retrieve the duplicate records based in one or two fields of my table.

Any help would be welcomed.

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
                                  Atenciosamente (Sincerely)
                        Ezequias Rodrigues da Rocha
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
A pior das democracias ainda é melhor do que a melhor das ditaduras
The worst of democracies is still better than the better of dictatorships
http://ezequiasrocha.blogspot.com/



--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
                                  Atenciosamente (Sincerely)
                        Ezequias Rodrigues da Rocha
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
A pior das democracias ainda é melhor do que a melhor das ditaduras
The worst of democracies is still better than the better of dictatorships
http://ezequiasrocha.blogs pot.com/ --=__Part4F6BC4E7.1__=--