Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-10-20
3
Medium Priority
?
141 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 59

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 59

Accepted Solution

by:
Julian Hansen earned 2000 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

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 article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
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…
Suggested Courses

715 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