How to delete duplicate rows in this case?

Question

|  id  |        name        |              date           |     points   |
|  10  |        Paul        |     2013-04-29 10:15:03     |       2      |
|  11  |       Joseph       |     2013-04-29 10:50:17     |       0      |
|  12  |       Joseph       |     2013-04-29 11:23:18     |       10     |
|  13  |        Bill        |     2013-04-29 11:27:10     |       8      |
|  14  |        Paul        |     2013-04-29 11:41:38     |       5      |
|  15  |       Joseph       |     2013-04-29 11:43:15     |       0      |
|  16  |       Joseph       |     2013-04-29 11:47:30     |       0      |
|  17  |       Joseph       |     2013-04-29 12:51:38     |       0      |
|  18  |       Joseph       |     2013-04-29 12:53:58     |       10     |
|  19  |        Bill        |     2013-04-29 13:17:10     |       8      |
|  20  |       Joseph       |     2013-04-29 13:21:38     |       7      |

Only the registers 16 and 17 must be deleted.

What I need is that every time there is a sequence of 0 from the same user, all of the same sequence are deleted, except the first 0, in this case, id number 15.

Are the ids always incremental? – Strawberry Apr 29 '13 at 16:42 — Strawberry, Apr 29 '13 at 16:42

Barranka · Answer 1 · 2013-04-29T18:35:36.237

2

Assuming that the date field is always incremental, you can follow the next procedure

Track the duplicate records and the minimum date of these records
Delete all the records that have date values greater than the minimum date.

A code example:

Step 1:

select name, points, count(id) as rowCount, min(id) as minId, min(`date`) as minDate
from yourTable
where points = 0
group by name
having count(id)>1

Step 2:

delete from yourTable
where id in (
    select id
    from yourTable
    inner join (
            select name, points, min(id) as minId, count(id) as rowCount, min(`date`) as minDate
            from yourTable
            where points = 0
            group by name
            having count(id) > 1
        ) as a on yourTable.name = a.name and yourTable.id > a.minId
    )
and points = 0;

Hope this helps

I think it might be useful to use temp tables to get the ids you want to delete:

-- Step 1: Create a temporary table with the names of the people you want to remove
drop table if exists temp_dup_names;
create temporary table temp_dup_names
    select name, points, min(id) as minId, count(id) as rowCount, min(`date`) as minDate
    from yourTable
    where points = 0
    group by name
    having count(id) > 1;
alter table temp_dup_names
    add index idx_name(name),
    add unique index idx_id(minId);

-- Step 2: Create a temporary table with the ids you want to delete
drop table if exists temp_ids_to_delete;
create temporary table temp_ids_to_delete
    select distinct a.id
    from yourTable as a
    inner join temp_dup_names as b on a.name=b.name and a.id > b.minId
    where points = 0;
alter table temp_ids_to_delete
    add unique index idx_id(id);

-- Step 3: Delete the rows
delete from yourTable
where id in (select id from temp_ids_to_delete);
-- If MySQL is configured in 'safe mode', you may need to add this 
-- to the where condition:
-- and id > 0;

edited Apr 29 '13 at 18:35

answered Apr 29 '13 at 16:08

Barranka

20,547
13
65
83

I do not understand why, but it did not work. Deleted many records, but still left many of the same user 0, in sequence. – Guttemberg Apr 29 '13 at 17:08
are both `id` and `date` fields incremental? (i.e. a greater `id` value is paired with a greater `date` value). I've edited the post to show a step-by-step solution using temp tables. Check step 2: The temp table should have the Ids you want to delete. If it doesn't have the right Ids, then step 1 must be checked again – Barranka Apr 29 '13 at 17:11
Yes, id and date are incremental – Guttemberg Apr 29 '13 at 17:15
@Guttemberg Ok. Try steps 1 and 2 of the second version of the solution, and check the Ids in the `temp_ids_to_delete` table (`select * from temp_ids_to_delete`). The Ids of the rows you want to delete should be there. – Barranka Apr 29 '13 at 17:20
@Guttemberg I tried this procedure on SQL Fiddle,, and I found that the rows that are to be deleted (acording to the data in your post), are ids 15, 16 and 17, because those are the duplicated Ids that fulfill the condition... the row with Id 11 is the first one where Joseph has 0 points... If you need to track the "sequence", then I suggest you use a high-level programming language to track the recors you need to remove. By the way, I modified the queries to filter only the 0 points records – Barranka Apr 29 '13 at 19:16
@Guttemberg check the SQL Fiddle exercise: http://sqlfiddle.com/#!2/41a07/3 – Barranka Apr 29 '13 at 19:16
Yes, the record should remain 11. Should only be deleted when they occur in sequence number 0. Assuming that a user has 11 records = 0, 12 = 0, 13 = 0, 14 = 0 and 15 = 3, records 12, 13 and 14 should be deleted. But if the user has the same records as: 20 = 10, 21 = 0, 22 = 5, 23 = 0, 24 = 7, 25 = 0, no record of that user should be deleted because "NO SEQUENCE 0" And this last example, 30 = 0, 31 = 8 (here ID of other users) .., .., 38 = 6 4 39 = 40 = 0 41 = 0 42 = 0 43 = 5 6 = 44, 45 = 0, 46 = 3 47 = 0 48 = 0 49 = 0, 50 = 2 , Records 46, 47, 48 and 49 should be deleted. – Guttemberg Apr 29 '13 at 19:32
Thank you. I'll check his example. – Guttemberg Apr 29 '13 at 19:33
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/29136/discussion-between-guttemberg-and-barranka) – Guttemberg Apr 29 '13 at 19:36

How to delete duplicate rows in this case?

1 Answers1