Solved

Find duplicates in column and remove entire row

Posted on 2012-03-24
5
293 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now