Duplicates...

[Kol]

Hi there!

How would i be able to find duplicates in a database based upon a particular column (email address), and show them?

Can you help?

Thanks!

[AstroTeg]

You didn't specify the database engine so I'm going to assume MySQL, but your mileage may vary...

SELECT emailaddress, COUNT(emailaddress) AS duplicatecount FROM mytablename WHERE COUNT(emailaddress) > 1 GROUP BY emailaddress

I haven't tested this and my only uncertainty is the WHERE clause - it looks like valid SQL but because we're doing a COUNT() and GROUP BY, it may get hung up on the WHERE.

But essentially, you group all the email addresses together and have SQL count them up using COUNT(). The WHERE clause only returns the ones you're interested in: the duplicates.

[Lars_Berg]

 
SELECT emailaddress, COUNT(emailaddress) AS duplicatecount FROM mytablename 
 GROUP BY emailaddress
having COUNT(emailaddress) > 1

[Kol]

Cool - Thanks...

Do you know how i would be able to get all of the duplicates?

For example;

[e-mail]
[e-mail]
[e-mail]

I would like it to return

[e-mail]
[e-mail]

Any ideas?

Thanks!

[Kol]

Dont worry - i worked it out.

I will get the duplicates with the query above, then stick another select query after it 😃

Thanks!