Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE tab1
    ("id" int, "email" varchar(18), "verified" varchar(4))
;
    
INSERT INTO tab1
    ("id", "email", "verified")
VALUES
    (1, 'jason@gmail.com', 'true'),
    (2, 'jason@gmail.com', 'none'),
    (3, 'benita@outlook.com', 'none'),
    (4, 'benita@outlook.com', 'none')
;
4 rows affected

DELETE FROM tab1
WHERE "id" IN 
(SELECT "id"
FROM (SELECT "id"
, ROW_NUMBER() OVER (PARTITION BY "email" ORDER BY "verified" DESC, "id") rn FROM tab1) t1
WHERE rn > 1)
2 rows affected

SELECt * FROM tab1


id
email
verified


1
jason@gmail.com
true

3
benita@outlook.com
none