SQL DELETE all rows except the last N rows for each unique value

Question

This is hard. How can I delete everything except the last one, for example 3 lines, for each unique value in a different field?

Here is the visual problem:

id | otherfield --------------- 1 | apple <- DELETE 2 | banana <- KEEP 3 | apple <- DELETE 4 | apple <- KEEP 5 | carrot <- KEEP 6 | apple <- KEEP 7 | apple <- KEEP 8 | banana <- KEEP 

How can I execute this in SQL?

Answer 1

Not tested, but something like these lines might work:

 DELETE t.* FROM table t JOIN ( SELECT id @rowNum := IF(@otherfield <> otherfield, 1, @rowNum + 1) rn, @otherfield := otherfield otherfield FROM ( SELECT id, otherfield FROM table ORDER BY otherfield, id DESC ) t, (SELECT @otherfield := NULL, @rowNum := -1) dm ) rs ON t.id = rs.id WHERE rs.rn > 3 

Answer 2

 Delete MyTable Where Id In ( Select Id From ( Select Id , (Select COUNT(*) From MyTable As T2 Where T2.OtherField = T.OtherField And T2.Id > T.Id) As Rnk From MyTable As T ) As Z Where Z.Rnk > 2 ) 

Another version, which may be slightly faster:

 Delete MyTable Where Id In ( Select T.Id From MyTable As T Left Join MyTable As T2 On T2.OtherField = T.OtherField And T2.Id > T.Id Group By T.Id Having Count(T2.Id) > 2 )