Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Find duplicates in column and remove entire row

Posted on 2012-03-24
5
Medium Priority
?
326 Views
Last Modified: 2012-03-24
I have a table that has a column that counts in order.  every now and then (haven't figured it out yet, I get a duplicate record.

I need to find and delete the duplicate records, what is the best way to do that?

Example

ID Product Value CountColumn
11 Test    12.43   1
12 Test2   14.54   2
13 Test 2  14.54   2
14  Test 3  13.64  3
0
Comment
Question by:theideabulb
[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
  • 3
5 Comments
 
LVL 2

Accepted Solution

by:
Kelden earned 2000 total points
ID: 37761410
Try this to select the double values:

select Product, value, count ( Product, Value )
from mytable
group by Product, Value
having COUNT( Product, Value ) > 1
0
 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 37761561
If you want to automate it:
Declare @id int
While exists (keldensthing)
Begin
Select top (1) @id=id from mytable
group by Product, Value
having COUNT( Product, Value ) > 1;
delete from mytable where id=@id;
End


If you want to choose which one gets deleted, add an order by to the select top(1) query.
0
 

Author Comment

by:theideabulb
ID: 37761582
here is the query i am actually doing and its giving me an error

select set_id, num_cnt, count(set_id, num_cnt)
from set_month_counts
group by set_id, num_cnt
having COUNT(set_id, num_cnt) > 1


 Warnings: --->
   W (1): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' num_cnt)
from set_month_counts
group by set_id, num_cnt
having COUNT(set_id, nu' at line 1
0
 

Author Comment

by:theideabulb
ID: 37761592
Here is actual data that is being returned, you can see that there are two #14 at the bottom.

id      set_id      num_cnt
1430535      49      1
1430536      49      2
1430537      49      3
1430538      49      4
1430539      49      5
1430540      49      6
1430541      49      7
1430542      49      8
1430543      49      9
1430544      49      10
1430545      49      11
1430546      49      12
1538007      49      13
1546862      49      14
1576386      49      14
0
 

Author Closing Comment

by:theideabulb
ID: 37762005
Not sure what was wrong with this query, but it was on the right path.  This here is what worked for me in the end.

select id,set_id, num_cnt
from set_month_counts group by set_id, num_cnt having count(*)>1
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

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…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

636 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