List:General Discussion« Previous MessageNext Message »
From:Sasha Pachev Date:June 23 1999 11:03am
Subject:Re: How to find duplicates in a table?
View as plain text  
Youngjoo Song wrote:
> 
> Hello,
> 
> I'm trying to find duplicate records in a table and extract them into
> another table. Anybody has a logic for finding duplicates? A detailed select
> statement would be appreciated, if that's possible. Thanks.
> 
> -- YJ
> 

Suppose we have table foo(n int, m int) and some pair of (n,m) are
identical. To find them:

SELECT t1.n,t1.m, count(*) as c FROM foo as t1, foo as t2 
 WHERE 
  t1.m = t2.m AND t1.n = t2.n GROUP BY t1.m, t1.n HAVING c > 1;

This is actually tested. c contains the square of the number duplicate
rows.

-- 
Sasha Pachev
http://www.sashanet.com/ (home)
http://www.direct1.com/ (work)
Thread
How to find duplicates in a table?Youngjoo Song23 Jun
  • Re: How to find duplicates in a table?Benjamin Pflugmann23 Jun
  • Re: How to find duplicates in a table?Sasha Pachev23 Jun
    • RE: How to find duplicates in a table?Youngjoo Song24 Jun
Re: How to find duplicates in a table?Kevin Smith23 Jun
Re: How to find duplicates in a table?(Jairo Souto)24 Jun
  • Re: How to find duplicates in a table?Sasha Pachev24 Jun