MySQL Server Forum - find and print out the duplicate record

Author

find and print out the duplicate record

cbs

2005-06-07, 8:23 pm

hi all,

do you know how to print out the duplicate rec ?

i m using the syntax

select id,IssueDate,ReportN
ame,RevNo,Factory,
count(*) from mytable group by
IssueDate,ReportName
,RevNo,Factory
having count(*) > 1;

* if i added the field "id" and group by, nothing print out.
because " id" is uquine. PK

now i can print out only 1 record which has duplicate in 4 times.

but in need to pirnt out all duplicate rec to the screen idendifed by the
id,
any idea ?

thanks
regards,
Bily

~
~
~

Bart Van der Donck

2005-06-11, 11:23 am

cbs wrote:

> do you know how to print out the duplicate rec ?
>
> i m using the syntax
>
> select id,IssueDate,ReportN
ame,RevNo,Factory,
> count(*) from mytable group by
> IssueDate,ReportName
,RevNo,Factory
> having count(*) > 1;
>
> now i can print out only 1 record which has
> duplicate in 4 times.
> but in need to pirnt out all duplicate rec to
> the screen idendifed by the id, any idea ?

I believe the following should do the trick:

SELECT
t1.*
FROM
mytable AS t1, mytable AS t2
WHERE
t1.IssueDate = t2.IssueDate
AND
t1.ReportName = t2.ReportName
AND
t1.RevNo = t2.RevNo
AND
t1.Factory = t2.Factory
AND
t1.id != t2.id
GROUP BY
id;

A small test case:

SELECT * FROM mytable;

id | IssueDate | ReportName | RevNo | Factory
1 | 2005-05-05 | dailystats | 5 | suse
2 | 2005-05-05 | dailystats | 5 | suse
3 | 2005-05-05 | dailystats | 5 | suse
6 | 2005-05-05 | dailystats | 5 | suse
4 | 2005-05-08 | security | 4 | caldera
5 | 2005-05-07 | learning | 3 | redhat
7 | 2005-05-07 | shells | 14 | solaris
8 | 2005-05-07 | learning | 3 | redhat
9 | 2005-05-05 | dailystats | 5 | microsoft

SELECT
t1.*
FROM
mytable AS t1, mytable AS t2
WHERE
t1.IssueDate = t2.IssueDate
AND
t1.ReportName = t2.ReportName
AND
t1.RevNo = t2.RevNo
AND
t1.Factory = t2.Factory
AND
t1.id!=t2.id
GROUP BY
id;

id | IssueDate | ReportName | RevNo | Factory
1 | 2005-05-05 | dailystats | 5 | suse
2 | 2005-05-05 | dailystats | 5 | suse
3 | 2005-05-05 | dailystats | 5 | suse
5 | 2005-05-07 | learning | 3 | redhat
6 | 2005-05-05 | dailystats | 5 | suse
8 | 2005-05-07 | learning | 3 | redhat

--
Bart

andreas.maurer1971@web.de

2005-06-13, 3:23 am

Bart Van der Donck schrieb:[color=darkr
ed]
> cbs wrote:
>

Hi Bart,

have a look at a recent posting in this newsgroup regarding the same
question.
Its title was: "Best way for finding duplicate entries in table?" and
it started on June 1st with Bill Karwin, myself and Aggro as posters.
Bill showed the best ways to print them out or to delete the
duplicates, at least, if you have a MySQL version that allows
Sub-SELECTs.

HTH,

Andy

Sponsored Links