Solved

In MySQL I need to delete some duplicates but leave more than 1

Posted on 2014-10-20
3
136 Views
Last Modified: 2014-10-21
I have a table with a bunch of duplicate records.  This is by design.  A sample may look like:

ID     Date                  location
-----------------------------------------
1      2014-10-20       1
2      2014-10-20       1
3      2014-10-20       1
4      2014-10-20       1
5      2014-10-20       1
6      2014-10-20       1
7      2014-10-20       2
8      2014-10-20       2
9      2014-10-20       2
10    2014-10-20       2
11    2014-10-20       2
12    2014-10-20       2
13      2014-10-21       1
14      2014-10-21       1
15      2014-10-21       1
16      2014-10-21       1
17      2014-10-21       1
18      2014-10-21       1
19      2014-10-21       2
20      2014-10-21       2
21      2014-10-21       2
22      2014-10-21       2
23      2014-10-21       2
24      2014-10-21       2

I want to remove only a certain number of duplicates.  For instance, each location has 6 duplicate dates (6 records for location 1 on 2014-10-20).

I would like to remove just TWO of the duplicate records from each date-location grouping leaving me with 4 records at location 1 on 2014-10-20.  I need to do this for several hundred such groupings.

The ID is primary and unique.

Any help?
0
Comment
Question by:pmascari
[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
  • 2
3 Comments
 
LVL 56

Expert Comment

by:Julian Hansen
ID: 40393841
Can you not  do something like this
CREATE TEMPORARY TABLE deleteids (id INT);
INSERT INTO deleteids (id) SELECT MAX(id) FROM t1 GROUP BY DATE;
DELETE FROM t1 WHERE id IN (SELECT id FROM deleteids);

Open in new window

And then just run the INSERT and DELETE query twice?
0
 
LVL 8

Author Comment

by:pmascari
ID: 40394452
Thanks for the comment.  Your suggestion could work if there were always 6 duplicates.  I've found that these groups can have up to 12 duplicates.  How can I delete a variable number of records, leaving me with groups of 4 for each date/location?
0
 
LVL 56

Accepted Solution

by:
Julian Hansen earned 500 total points
ID: 40394518
Then something like this
SET @ROW := 0, @DATE := '';
DELETE FROM t1 WHERE id IN  (
SELECT id FROM (
  SELECT id, `date`,
      @ROW := IF(@DATE = `date`, @ROW + 1, 1) AS row_number,
      @DATE := `date` AS dummy
  FROM t1
  ORDER BY `date`
) subq
WHERE subq.row_number > 4
);

Open in new window

0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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 …
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…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

749 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