?
Solved

Find duplicates in column and remove entire row

Posted on 2012-03-24
5
Medium Priority
?
320 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

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
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…

770 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