Solved

Remove Duplicates From A List

Posted on 2014-01-30
7
219 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

696 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