Solved

Remove Duplicates From A List

Posted on 2014-01-30
7
216 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Query Help - MSSQL - Averages 5 30
Incorrect definition of table mysql.proc 7 53
Present Absent from working date rage 11 38
Filtering characters in an SQL field 2 16
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

820 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