Finding Duplicate records

[cancer10]

Hi

I have a table called tbl_members with 5,000 records

I have 3 colums,

memberID
member_name
email_address

I want to group all members who have similar email address. The select clause should also include memberID and member_name.

Can you help me setting up a query for this please?

Thanx

[bogocles]

Try this ... (I'm assuming two things: 1) you're using SQL Server, 2) you don't have a Record Number column in your members table).


CREATE TABLE #tmp_duplicates(
email_address VARCHAR(256) NOT NULL,
occurences INT NOT NULL
)

INSERT INTO
#tmp_duplicates
SELECT
email_address,
count(*)
FROM
tbl_members
GROUP BY
email_address

SELECT
mbr.*
FROM
tbl_members [mbr]
INNER JOIN #tmp_duplicates [tmp]
ON tmp.email_address = mbr.email_address
WHERE
tmp.occurences > 1

[chazzy]

you'll have to specify what makes email addresses similar.

it could be as simple as


select memberID, member_name, email_address
from tbl_members
where email_address in (select distinct email_address from tbl_members having count(*) > 1 group by email_address);