How to delete duplicate rows with SQL? How to delete duplicate rows with SQL? sql sql

How to delete duplicate rows with SQL?


DELETE FROM table WHERE id NOT IN    (SELECT MAX(id) FROM table GROUP BY date);


I don't have comment rights, so here's my comment as an answer in case anyone comes across the same problem:

In SQLite3, there is an implicit numerical primary key called "rowid", so the same query would look like this:

DELETE FROM table WHERE rowid NOT IN(SELECT MAX(rowid) FROM table GROUP BY date);

this will work with any table even if it does not contain a primary key column called "id".


For mysql,postgresql,oracle better way is SELF JOIN.

Postgresql:DELETE FROM table t1 USING table t2 WHERE t1.date=t2.date AND t1.id<t2.id;MySQL        DELETE FROM tableUSING table, table as vtableWHERE (table.id < vtable.id)AND (table.date=vtable.date)

SQL aggregate (max,group by) functions almost always are very slow.