Re: selecting duplicate records

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