Re: How to find duplicates in a table?

[Sasha Pachev]

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  [phone]
> 

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)