find and print out the duplicate record

[cbs]

hi all,

do you know how to print out the duplicate rec ?

i m using the syntax

select id,IssueDate,ReportName,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]

cbs wrote:

do you know how to print out the duplicate rec ?

i m using the syntax

select id,IssueDate,ReportName,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]

Bart Van der Donck schrieb:

cbs wrote:

do you know how to print out the duplicate rec ?

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