List all duplicate values

Databases
1

Hi Guys,

I have many duplicate email address in my table. My current sql get’s me the total occurrence for each duplicate value, but I need to list all my duplicate email address so rather than grouping them I just want to list them. Any help appreciated.


SELECT
FirstName,
LastName,
Email,
count(Email) AS Occurance,
Company,
Country,
FROM dedupe
GROUP BY Email
HAVING ( COUNT(Email) > 1 );

Thanks

2 

SELECT
    d1.FirstName
  , d1.LastName
  , d1.Email
  , d1.Company
  , d1.Country
FROM dedupe AS d1
INNER JOIN 
  (SELECT Email
   FROM dedupe
   GROUP BY Email
   HAVING COUNT(*) > 1
  ) AS d2
ON d1.Email = d2.Email
ORDER BY d1.Email
3

thanks Guido, I’m running it but its terrible slow…

4

Do you have an index on email?

5

no index, but it only took about 2 minutes and your sql did the job man :slight_smile:

Graci