delete_duplicates.sql

create table test (
    id int auto_increment primary key,
    email varchar(20)
);
-- Query OK, 0 rows affected (0.02 sec)

insert into test
    (id, email)
values
    (1, 'aaa'),
    (2, 'bbb'),
    (3, 'ccc'),
    (4, 'bbb'),
    (5, 'ddd'),
    (6, 'eee'),
    (7, 'aaa'),
    (8, 'aaa'),
    (9, 'eee');
-- Query OK, 9 rows affected (0.01 sec)
-- Records: 9  Duplicates: 0  Warnings: 0

select * from test;
-- +----+-------+
-- | id | email |
-- +----+-------+
-- |  1 | aaa   |
-- |  2 | bbb   |
-- |  3 | ccc   |
-- |  4 | bbb   |
-- |  5 | ddd   |
-- |  6 | eee   |
-- |  7 | aaa   |
-- |  8 | aaa   |
-- |  9 | eee   |
-- +----+-------+
-- 9 rows in set (0.00 sec)

delete from test
where id not in (
    select * from (
        select max(id)
        from test
        group by email
    ) as keep_ids
);
-- Query OK, 4 rows affected (0.01 sec)

select * from test;
-- +----+-------+
-- | id | email |
-- +----+-------+
-- |  3 | ccc   |
-- |  4 | bbb   |
-- |  5 | ddd   |
-- |  8 | aaa   |
-- |  9 | eee   |
-- +----+-------+
-- 5 rows in set (0.00 sec)

drop table test;
-- Query OK, 0 rows affected (0.02 sec)