Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 228
  • Last Modified:

Remove Duplicates From A List

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
U_S_A
Asked:
U_S_A
1 Solution
 
Ken ButtersCommented:
Select Distinct <field> from ....

where field is the email address you have above...
0
 
U_S_AAuthor Commented:
@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
 
John_VidmarCommented:
select <fieldname> from <table> group by <fieldname> having count(*) = 1
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
Ken ButtersCommented:
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
 
U_S_AAuthor Commented:
@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
 
Ken ButtersCommented:
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
 
Tomas Helgi JohannssonCommented:
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now