asked on

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

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?

Julian Hansen

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?

pmascari

ASKER

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?

ASKER CERTIFIED SOLUTION

Julian Hansen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial