title: SQL DELETE all rows apart from last N rows for each unique value
body: <p>Here's a tough one,
  How would I delete all but the last, say 3 rows, for each unique value in a different field?</p>
  
  <p>Here's a visual of the problem:</p>
  
  <pre><code>id | otherfield
  ---------------
   1 | apple      &lt;- DELETE
   2 | banana     &lt;- KEEP
   3 | apple      &lt;- DELETE
   4 | apple      &lt;- KEEP
   5 | carrot     &lt;- KEEP
   6 | apple      &lt;- KEEP
   7 | apple      &lt;- KEEP
   8 | banana     &lt;- KEEP
  </code></pre>
  
  <p>How would I accomplish this in SQL?</p>