SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE contacts ( uid varchar(100), email varchar(100), ts bigint ); INSERT INTO contacts (email,uid,ts) VALUES ('aaa@aaa.com','a',unix_timestamp()); INSERT INTO contacts (email,uid,ts) VALUES ('bbb@aaa.com','b',unix_timestamp()); INSERT INTO contacts (email,uid,ts) VALUES ('cc@aaa.com','c',unix_timestamp()); INSERT INTO contacts (email,uid,ts) VALUES ('bbb@aaa.com','b',unix_timestamp()); INSERT INTO contacts (email,uid,ts) VALUES ('aaa@aaa.com','a',unix_timestamp()); INSERT INTO contacts (email,uid,ts) VALUES ('aaa@aaa.com','a',unix_timestamp()); INSERT INTO contacts (email,uid,ts) VALUES ('ccc@aaa.com','c',unix_timestamp()); INSERT INTO contacts (email,uid,ts) VALUES ('aaa@aaa.com','a',unix_timestamp()); INSERT INTO contacts (email,uid,ts) VALUES ('bbb@aaa.com','b',unix_timestamp()); INSERT INTO contacts (email,uid,ts) VALUES ('ccc@aaa.com','c',unix_timestamp()); INSERT INTO contacts (email,uid,ts) VALUES ('ddd@aaa.com','d',unix_timestamp()); delete a from contacts a left join( select max(ts) maxtimestamp, email, uid from contacts group by uid, email) b on a.ts = maxtimestamp and a.uid = b.uid and a.email = b.email where b.maxtimestamp IS NULL; SELECT * FROM contacts;