title: SQL Server Duplicate Records deleting oldest record and keeping newest
body: <p>I have a table in SQL Server similar to this:</p>
  
  <pre><code>Emp#        CourseID        DateComplete        Status
  1           Course1         21/05/2012          Failed
  1           Course1         22/05/2012          Passed
  2           Course2         22/05/2012          Passed
  3           Course3         22/05/2012          Passed
  4           Course1         31/01/2012          Failed
  4           Course1         28/02/2012          Passed
  4           Course2         28/02/2012          Passed
  </code></pre>
  
  <p>Trying to capture the newest record for each course for each emp#. And if the same course has been attempted on the same day capture the 'passed' course record.</p>
  
  <p>Thinking something along these lines:</p>
  
  <pre><code>SELECT DISTINCT .....
          INTO Dup_Table
          FROM MainTable
  GROUP BY ........
  HAVING COUNT(*) &gt; 1
  
  DELETE MainTable
          WHERE Emp# IN (SELECT Emp# FROM Dup_Table)
  
  INSERT MainTable SELECT * FROM Dup_Table
  
  Drop Table Dup_Table
  GO
  </code></pre>
  
  <p>But not sure if this is the </p>
  
  <ol>
  <li>the best approach and</li>
  <li>how to bring the Emp#/courseID/DateComplete/Status all together.</li>
  </ol>