title: Mysql only keep the highest value per id per value
body: <p>I have a database that looks like this:</p>
  
  <p><code>post metrics minutes</code>(There is only data for post id 1 in this example)</p>
  
  <pre><code>| post id | date updated local    | reach |
  |1        | 2018-01-01 01:00:00   | 10    |
  |1        | 2018-01-01 01:05:00   | 20    |
  |1        | 2018-01-01 01:15:00   | 22    |
  |1        | 2018-01-01 16:05:00   | 100   |
  |1        | 2018-01-02 03:00:00   | 121   |
  |1        | 2018-01-02 21:00:00   | 140   |
  |1        | 2018-01-04 01:00:00   | 147   |
  </code></pre>
  
  <p>My system is designed to fetch data for all posts every 5 minutes and put the results in the above table if the reach is not the same as the last time it was stored for that post (this to prevent getting a shitload of data that is exactly the same). </p>
  
  <p>Now there are thousands of posts and the table start to  grow out of control making my website lot slower when loading the data from this table.</p>
  
  <p>So I decided that I can decrease the data by only keeping the last row per post per day, so I want to delete all rows that are not the max <code>date updated local</code> for that post. The result would be:</p>
  
  <pre><code>| post id | date updated local    | reach |
  |1        | 2018-01-01 16:05:00   | 100   |
  |1        | 2018-01-02 21:00:00   | 140   |
  |1        | 2018-01-04 01:00:00   | 147   |
  </code></pre>
  
  <p>I came up with:</p>
  
  <pre><code>DELETE FROM `post metrics minutes` 
  WHERE EXISTS (
      SELECT *
      FROM `post metrics minutes` pmmtemp
      WHERE pmmtemp.`post id` = `post metrics minutes`.`post id`
      AND pmmtemp.`date updated local` &gt; `post metrics minutes`.`date updated local`
      AND DATE(pmmtemp.`date updated local`) = DATE(`post metrics minutes`.`date updated local`)
      );
  </code></pre>
  
  <p>But this gives me the following error:</p>
  
  <p><code>Error Code: 1093. Table 'post metrics minutes' is specified twice, both as a target for 'DELETE' and as a separate source for data</code></p>
  
  <p>Hope anyone can help me out!</p>