How to remove a duplicate row in SQL with an older date field

Question

I have two rows in my table which are exact duplicates with the exception of a date field. I want to find these records and delete the older record by hopefully comparing the dates.

For example I have the following data

    ctrc_num | Ctrc_name   | some_date
   ---------------------------------------
        12345 | John R      | 2011-01-12
        12345 | John R      | 2012-01-12
        56789 | Sam S       | 2011-01-12
        56789 | Sam S       | 2012-01-12

Now the idea is to find duplicates with a different 'some_date' field and delete the older records. The final output should look something like this.

      ctrc_num | Ctrc_name   | some_date
   ---------------------------------------
        12345 | John R      | 2012-01-12
        56789 | Sam S       | 2012-01-12

Also note that my table does not have a primary key, it was originally created this way, not sure why, and it has to fit inside a stored procedure.

Answer 1

If you look at this:

SELECT * FROM <tablename> WHERE some_date IN
(
SELECT MAX(some_date) FROM <tablename> GROUP BY ctrc_num,ctrc_name
HAVING COUNT(ctrc_num) > 1 
AND COUNT(ctrc_name) > 1
)

You can see it selects the two most recent dates for the duplicate rows. If I switch the select in the brackets to 'min date' and use it to delete then you are removing the two older dates for the duplicate rows.

DELETE FROM <tablename> WHERE some_date IN
(
SELECT MIN(some_date) FROM <tablename> GROUP BY ctrc_num,ctrc_name
HAVING COUNT(ctrc_num) > 1 
AND COUNT(ctrc_name) > 1
)