Solved

Find duplicates in column and remove entire row

Posted on 2012-03-24
5
304 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
  • 3
5 Comments
 
LVL 2

Accepted Solution

by:
Kelden earned 500 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 38

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

803 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