title: A query to delete duplicates with GROUP BY
body: <pre><code>id_specific_price    id_product  
  -------------------------------
              1                2  
              2                2  
              3                2  
              4                3  
              5                3  
              6                3  
              7                3
  </code></pre>
  
  <p>Need to delete the duplicates, expected outcome:</p>
  
  <pre><code>id_specific_price    id_product  
  -------------------------------
              3                2  
              7                3
  </code></pre>
  
  <hr>
  
  <pre><code>SELECT * 
    FROM ps_specific_price 
   WHERE id_specific_price NOT IN 
   (SELECT MAX(id_specific_price) 
      FROM ps_specific_price 
     GROUP BY id_product) 
  </code></pre>
  
  <p>works but </p>
  
  <pre><code>DELETE FROM ps_specific_price 
   WHERE id_specific_price NOT IN 
  (SELECT MAX(id_specific_price) 
     FROM ps_specific_price 
    GROUP BY id_product)
  </code></pre>
  
  <p>does not. There are plenty of examples to get around this but for some reason I am not able to adapt it. I believe it is GROUP BY. For example:</p>
  
  <pre><code>DELETE FROM ps_specific_price 
   WHERE id_specific_price NOT IN
   (SELECT MAX(p.id_specific_price) 
      FROM (SELECT * FROM ps_specific_price ) as p)
     GROUP BY id_product
  </code></pre>
  
  <p>Where did I go wrong here?</p>