title: Delete all rows except max(timestamp) on each day in mysql
body: <p>How to delete all rows except the max(timestamp) per currency, of each day. So the query must only keep the most recent row per currency, per day.</p>
  
  <p>Data</p>
  
  <pre><code>Id,currency,value,timestamp
  21,btc,8000,2018-07-25 23:00:00 --&gt; Keep this row
  20,eth, 800,2018-07-25 23:00:00 --&gt; Keep this row
  19,btc,7999,2018-07-25 22:00:00
  18,eth, 799,2018-07-25 22:00:00
  17,btc,7998,2018-07-25 21:00:00
  16,eth, 798,2018-07-25 21:00:00
  15,btc,7997,2018-07-24 23:00:00 --&gt; Keep this row
  14,eth, 800,2018-07-24 23:00:00 --&gt; Keep this row
  13,btc,7996,2018-07-24 22:00:00
  12,eth, 799,2018-07-24 22:00:00
  11,btc,7995,2018-07-24 21:00:00
  10,eth, 798,2018-07-24 21:00:00
  </code></pre>
  
  <p>Latest (max(created_at)) row per currency, per day</p>
  
  <pre><code>SELECT
    t.currency
   , DATE(t.created_at)
   , MAX(t.created_at)
  
  FROM `tbltest` t
  
  GROUP BY 
     t.currency
   , DATE(t.created_at)
  </code></pre>
  
  <p>I can find the latest (max(created_at)) row per currency, per day, but how do I only keep those ones, and delete all the other ones?</p>
  
  <p><strong>solution</strong></p>
  
  <p>Since the highest timestamp also has the highest id, the simple solution used was the following:</p>
  
  <pre><code>DELETE FROM `tbltest` 
  WHERE id NOT IN 
      (SELECT MAX(id) 
         FROM (select * FROM `tbltest`) as t2 
     GROUP BY currency, DATE(created_at))
  </code></pre>