How to delete duplicate rows with SQL?
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.