List:General Discussion« Previous MessageNext Message »
From:Sasha Pachev Date:June 24 1999 10:55am
Subject:Re: How to find duplicates in a table?
View as plain text  
Jairo Souto wrote:
> 
> sasha@stripped wrote:
> > 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)
> >
> 
> why not
>         select n, m, count(*) as c from foo group by n, m having c > 1
> ?
> 
> --Jairo Souto  jsouto@stripped  (038)9971-0014
> 

You are right, although the original statment solve the problem, it is
more complex and inefficient than the one you have given.

Back to the actual table, to make it work for this table:

CREATE TABLE session (
  uid smallint(6) DEFAULT '0' NOT NULL auto_increment,
  DATE char(12) DEFAULT '' NOT NULL,
  TIME char(10) DEFAULT '' NOT NULL,
  ID char(22) DEFAULT '' NOT NULL,
  PRIMARY KEY (uid),
  KEY uid (uid),
  UNIQUE uid_2 (uid)
);


well, we have to re-define the meaning of "duplicate" - probably all
rows with the same date, time and id - with this definition:

SELECT date, time, id, count(*) as n from session GROUP BY date,time,id
HAVING n > 1;

-- 
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