From: Christopher Browne <cbbrowne@acm.org>
To: pgsql-sql@postgresql.org
Subject: Re: selecting duplicate records
Date: Mon, 22 Sep 2003 23:13:23 -0400
Message-ID: <m3r8282o4s.fsf@wolfe.cbbrowne.com> (raw)
References: <3F6EF83A.129807D6@rodos.fzk.de>
The world rejoiced as ch@rodos.fzk.de (Christoph Haller) wrote:
>> 1. How to select duplicate records only from a single table using a
> select
>> query.
>>
> e.g.
> select sid,count(sid) from location group by sid having count(sid)>1;
>
> Do you get the idea?
> Your request is pretty unspecific, so if this is not what you're asking
> for,
> try again.
The aggregate is likely to perform horrifically badly. Here might
be an option:
Step 1. Find all of the duplicates...
select a.* into temp table sid from some_table a, some_table b
where a.oid < b.oid and
a.field1 = b.field1 and
a.field2 = b.field2 and
a.field3 = b.field3 and
...
a.fieldn = b.fieldn;
Step 2. Look for the matching entries in the source table...
select a.* from some_table a, sid b
where
a.field1 = b.field1 and
a.field2 = b.field2 and
a.field3 = b.field3 and
...
a.fieldn = b.fieldn;
[There's a weakness here; if there are multiple dupes, they may get
picked multiple times in the second query :-(.]
--
If this was helpful, <http://svcs.affero.net/rm.php?r=cbbrowne; rate me
http://cbbrowne.com/info/rdbms.html
As Will Rogers would have said, "There is no such thing as a free
variable." -- Alan Perlis
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: cbbrowne@acm.org
Subject: Re: selecting duplicate records
In-Reply-To: <m3r8282o4s.fsf@wolfe.cbbrowne.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox