Find Duplicate rows

[mcauliff]

I have a DB2 v7 table, that I need to find duplicate email addresses. The primary key to the table is a autogenerated number.

How would I code the SQL to perform this task? I have tried a join and subselect, but didn't work or return multiple rows.

 

[MarcLodge]

mcauliff,

Howabout?

SELECT EMAIL_ADDR, COUNT(*)
FROM EMAIL_TAB
GROUP BY EMAIL_ADDR
HAVING COUNT(*) > 1

Marc

 

[mcauliff]

Thank you for the reply.

I resolved it with the following SQL

SELECT EMAIL_ID
,FIRST_NM || ' ' || LAST_NM
,CLIENT_NO
,CNTC_NO
FROM Table
WHERE (EMAIL_ID) IN
(
SELECT EMAIL_ID FROM Table
GROUP BY EMAIL_ID

 

[MarcLodge]

mcauliff,

A couple of points:
a) Your SQL is missing a bracket (after the subselect I suspect)
b) doesn't that give you a list of ALL email addresses, removing the duplicates, which is not what I believe you asked for.

Pleased that you found an answer though, congrats and thanks for letting us know.

Marc