Delete all except the latest

from the Artful Common Queries page


In a table transactons( bizid int, txdatetime, ... ). you wish to delete all rows except the latest row per bizid. Do you need to build a list of all but the latest transactions per bizid? No. Just join the table to an aggregating query excluding the latest transaction for each bizid ...
delete t
from transactions as t
join ( 
  select bizid, max(txdatetime) as latest 
  from transactions
  group by bizid
) m on t.bizid=m.bizid and t.txdatetime < m.latest;

Return to the Artful Common Queries page