By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE person (
id int,
email varchar(25)
);
insert into person values
(1,'john@example.com'),
(2,'bob@example.com'),
(3,'john@example.com');
3 rows affected
select email, max(id) as id
from person
group by email
having count(1) > 1
| id | |
|---|---|
| john@example.com | 3 |
delete from person
where id in (
select max(id) as id
from person
group by email
having count(1) > 1
);
1 rows affected
select *
from person;
| id | |
|---|---|
| 1 | john@example.com |
| 2 | bob@example.com |