I've got table with this data
id, archive id, ean, index, date, (...)
I've got some items with same archive id, same ean, but different index.
So in this case, I want to delete older (basing on date) item, so result will be that for each combination archive_id/index there will be no more than 1 result.
The following (untested) should work:
DELETE FROM someTable WHERE EXISTS ( SELECT id FROM someTable AS subqTable WHERE
subqTable.id = someTable.id
AND subqTable.ean = someTable.ean
-- and other equality comparisons
AND subqTable.date AFTER someTable.date)
*.DELETE duplicates.*
FROM _table
JOIN _table AS duplicates
ON (_table.archive_id = duplicates.archive_id AND _table.index = duplicates.index)
WHERE duplicates.date < _table.date;
delete t1
from your_table t1
left join
(
select archive_id, ean, min(date) as mdate
from your_table
group by archive_id, ean
) t2 on t1.archive_id = t2.archive_id
and t1.ean = t2.ean
and t1.date = t2.mdate
where t2.mdate is null
