Solved

Remove Duplicates From A List

Posted on 2014-01-30
7
214 Views
Last Modified: 2014-02-02
I have a list that i need to remove all Duplicates, if I find any,
I need to do this in mysql, due to the sheer number of records - 150,000
Can someone help me with a query to eliminate all duplicates.
See the example goal that I'm looking for below.  Thank you.

DATA
bob@bob.com
bob@bob.com
sue@sue.com
mary@mary.com
mary@mary.com
joe@joe.com
sally@sally.com


GOAL
sue@sue.com
joe@joe.com
sally@sally.com
0
Comment
Question by:U_S_A
7 Comments
 
LVL 19

Expert Comment

by:Ken Butters
ID: 39821225
Select Distinct <field> from ....

where field is the email address you have above...
0
 

Author Comment

by:U_S_A
ID: 39821472
@Ken Butters,

Will this give me only records which are NOT duplicated?  or will it give me all unique records.  

I don't want any record that is listed more than once.

Thank you.
0
 
LVL 11

Accepted Solution

by:
John_Vidmar earned 500 total points
ID: 39821527
select <fieldname> from <table> group by <fieldname> having count(*) = 1
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 19

Expert Comment

by:Ken Butters
ID: 39821537
Distinct will give you all unique records.  

If it occurs more than once... it will be only be returned once. (dups removed)

If there is only one occurrence... it will be returned once.
0
 

Author Comment

by:U_S_A
ID: 39821573
@Ken

I want to remove any that have more than once.  I don't want to thin out the database to show only unique records.

How can I do this, please?
0
 
LVL 19

Expert Comment

by:Ken Butters
ID: 39821736
Sorry... I wasn't reading close enough.... and answered what I thought you were asking instead of what you really asked.

John's answer above should do what you want.

select <fieldname> from <table> group by <fieldname> having count(*) = 1

where fieldname is the email field in your table.

His query will first get all the records and group them by email address, and then only select the records where the count is equal to 1.

In the case where there is a duplicate... like

bob@bob.com
bob@bob.com

In that case count(*) will be > 1 and will not be returned.
0
 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 39822204
Hi!

Using John's query further you simply execute

delete from <table>
where <fieldname> not in ( select <fieldname> from <table> group by <fieldname> having count(*) = 1)

This will remove all duplicate rows.

Regards,
    Tomas Helgi
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to simplify my SQL statement? 14 50
[MYSQL]: Delete is very slow 4 54
Alter an update query which rounds 7 30
mcrypt_create_iv() is deprecated 4 67
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Creating and Managing Databases with phpMyAdmin in cPanel.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now