Considering a simple table with id-datetime field. I want to remove all records, where datetime is smaller than some constant, but keep ids which have at least one record with bigger date.
My first effort was
DELETE FROM journal WHERE id IN ( SELECT j.id FROM journal j WHERE NOT EXIST (SELECT * FROM journal j2 WHERE j.id == j2.id AND j2.created_at >= 636742944000000000 )
Which worked poorly. Then I rewrote is as following
DELETE FROM journal WHERE id IN ( SELECT j.id FROM journal j GROUP BY id HAVING MAX(created_at) < 636742944000000000; )
Which behaved much better.
But I still wonder what's the most performant way to do so? Maybe some vendor-specific query extensions?