Remove duplicates records but keep record with minimum ID

Question

I have some duplicate records in a table I would like to remove but keep the minimum ID record in the table as the unique record.

The query to return the minimum ID records I want to keep is as follows:

select 

    MIN(ID) AS ID
,   Date
,   Portfolio
,   Instrument
,   Dimension
,   COUNT(ID) 

from mytable
group by date, Portfolio, Instrument, Dimension
having COUNT(ID) > 1
order by date, Portfolio, Instrument, Dimension

However i'm unser as to how to write the query to ensure that I delete all the duplicates except the MIN(ID) records above. I'm guessing it is going to have to be a sub query against all the IDs that are duplicates except the above?

score 2 · Accepted Answer · answered Mar 28 '19 at 12:57

Updatable CTEs and row_number():

with todelete as (
      select t.*,
             row_number() over (partition by date, Portfolio, Instrument, Dimension order by id) as seqnum
      from mytable t
     )
delete from todelete
    where seqnum > 1;

The CTE enumerates the rows in the table based on the four columns, with the minimum id getting a value of 1. The outer where clause removes all columns with an enumeration value > 1.

score 1 · Answer 2 · answered Mar 28 '19 at 12:57

Use row_number() :

delete t
from (select t.*,
             row_number() over (partition by date, Portfolio, Instrument, Dimension order by id) as seq
      from mytable
     ) t
where seq > 1;

By this way you will get only the records which have a minimum id.

Remove duplicates records but keep record with minimum ID

2 Answers2