T-SQL Specific Syntax problem (Simple no doubt)

Question

Hi guys,

I have an issue with a query I'm trying to run on some data, I guess the place to start is to describe the data.

Ok so I have a list of email addresses, each email address has a unique ID and an account ID

Also in my tables I have a set number which auto incrememnts, this will allow me to target duplicate email addresses

What I need to do is something like this.

Insert into duplicates
   (EMAIL,ACCOUNTID,ID)

SELECT Email,AccountID,ID
   FROM EmailAddresses

Group by Email,AccountID
   Having Count(email)>1
Order by AccountID, Email

So essentially I want to select all duplicate email addresses and insert them (and their relative fields) into a new table broken down by accountID so I can run some further querys on it.

I have been battling with this for way too long and could just use a fresh perspective.

Cheers in advance

Answer 1

SELECT *
FROM EmailAddresses e1 INNER JOIN 
   (SELECT Email, AccountId
   FROM EmailAddresses
   GROUP BY Email, AccountID
   HAVING Count(Id)>1) Dups 
      ON Dups.Email = e1.Email AND Dups.AccountId = e1.AccountId

Answer 2

SELECT Email, AccountID, ID INTO duplicates
FROM EmailAddresses E
WHERE (SELECT COUNT(*) FROM EmailAddresses E1 WHERE E1.Email = E.Email) > 1
GROUP BY Email, AccountID, ID;

Answer 3

Make sure the ID field in the duplicates table is NOT an identity field and remove the order by clause.

Insert into duplicates 
(EMAIL,ACCOUNTID,ID) 
SELECT Email,AccountID, MIN(ID)
FROM EmailAddresses 
Group by Email,AccountID 
Having Count(email)>1

If you want all the duplicate records, use the select suggested by queen3