title: Delete duplicates based on Group By - SQL
body: <p>EDIT: I think I now have the solution but need to do some more sense checking...</p>
  
  <pre><code>DELETE TBLFIRE_TEMP3 FROM TBLFIRE_TEMP3
  LEFT OUTER JOIN (
     SELECT MIN(FireNo) as FireNo, ActionRef, FRADate, FIREUPRN
     FROM TBLFIRE_TEMP3 
     GROUP BY ActionRef, FRADate, FIREUPRN
  ) as KeepRows ON
     TBLFIRE_TEMP3.FireNo = KeepRows.FireNo
  WHERE
     KeepRows.FireNo IS NULL
  </code></pre>
  
  <p>-############### Previous Comments ###############</p>
  
  <p>I have a table which has duplicates in (based on three columns). I can find them and see them by doing the following and would then simply want to delete the duplicates (i.e. so all count(*) results are '1') </p>
  
  <pre><code>SELECT COUNT(*),ActionRef, FRADate, FIREUPRN
  FROM TBLTempTable
  GROUP BY ActionRef, FRADate, FIREUPRN
  </code></pre>
  
  <p>So I can see the count of how many times these groups occur. What I want to do is Delete the duplicates. I've tried the below but it deletes every row, even singular:</p>
  
  <pre><code>DELETE a FROM TblTempTable a JOIN
  (
    SELECT ActionRef, FRADate, FIREUPRN
      FROM TblTempTable 
     GROUP BY ActionRef, FRADate, FIREUPRN
  ) d 
     ON (a.ActionRef = b.ActionRef
    AND a.FRADate = b.FRADate
  AND a.FIREUPRN = b.FIREUPRN)
  </code></pre>
  
  <p>Based on the codes I've looked at the guide me I believe I am close but currently it deletes everything.</p>
  
  <p>References:
  SQL- 
  <a href="https://stackoverflow.com/questions/18932/how-can-i-remove-duplicate-rows">How can I remove duplicate rows?</a> 
  <a href="https://stackoverflow.com/questions/7152333/group-by-does-not-remove-duplicates">GROUP BY does not remove duplicates</a> </p>
  
  <p>-These are MySQL so not to relevant in the end:</p>
  
  <p><a href="https://stackoverflow.com/questions/8622513/select-and-delete-rows-within-groups-using-mysql?rq=1">select and delete rows within groups using mysql</a>
  <a href="https://stackoverflow.com/questions/854128/find-duplicate-records-in-mysql?rq=1">Find duplicate records in MySQL</a></p>