title: How to easily select or mark latest duplicates in a SQL database
body: <p>I have access to a database that is populated from measurements tools. The item being measured is represented by a unique quadruplet of fields ('order', 'batch', 'lot ', 'item').</p>
  
  <p>An item is often re-measured a couple times until it shows a PASS, which means that the database contains sort-of "duplicates". These duplicates are kept on purpose (I can't get rid of them). The timestamp field is used to tell which measurement is the one to display (we only want the last one for each unique item)</p>
  
  <pre><code>order | batch | lot | item | tstamp | val1 | val2 | val3 |
  ----- | ----- |---- |----- |------- |----- |----- |----- |
  001   | A     | 1   | 1    | 12:10  |
  001   | A     | 1   | 2    | 12:11  |
  001   | A     | 1   | 3    | 12:12  | &lt;-- ignore/mark this one
  001   | A     | 1   | 3    | 12:13  | &lt;-- ignore/mark this one
  001   | A     | 1   | 3    | 12:14  |
  001   | A     | 2   | 1    | 12:15  |
  001   | A     | 2   | 2    | 12:16  | &lt;-- ignore/mark this one
  001   | A     | 2   | 2    | 12:17  |
  </code></pre>
  
  <p>My need here is to retrieve only the latest measurements of each item for a order (or order+batch, or order+batch+lot).</p>
  
  <p>From what I understood, using <strong>GROUP BY order, batch, lot, item</strong> will return one row per item but with no guarantee that is the one with the highest timestamp.</p>
  
  <p>The only solution I found yet is to perform a LEFT JOIN of the table on itself using (ON t1.order=t2.order AND t1.batch=t2.batchAND t1.lot=t2.lot AND t1.item=t2.item AND t1.tstamp
  
  <p>One idea I had was to setup a trigger on insertion that would mark oldest records of the same item as duplicates. I had to backpedal since the database engine is MySQL, and its is not possible to udpate the same table that fired the trigger.</p>
  
  <p>Do you have any suggestions how I could achieve this?</p>