If you have ever needed to add a unique key to a table in Oracle (I’m sure a similar query in SQL Server would work) but couldn’t because of duplicate rows, maybe the following query will help you.

Assume you want to create the following index

alter table FREIGHT_RATE
   add constraint FREIGHT_RATE_UK02 
   unique (CARRIER_EQUIP_TYPE_XREF_ID, 
           CARRIER_LANE_ID,  
           RATE_ADJ_ID, 
           CURRENCY_UOM_ID, 
           EFF_END_DTE)

but can’t because of duplicate records….

The following query will locate and delete duplicate records based on their eff_strt_dte (choosing the most recently added one as the one to keep)

DELETE FROM freight_rate
WHERE freight_rate_id IN(
  WITH unique_ids AS (
     SELECT * from (
        SELECT first_value(freight_rate_id) OVER(PARTITION BY
                                   CARRIER_EQUIP_TYPE_XREF_ID,
                                   CARRIER_LANE_ID,
                                   RATE_ADJ_ID,
                                   CURRENCY_UOM_ID,
                                        EFF_END_DTE
        ORDER BY rate_eff_strt_dte DESC) as ids
     FROM freight_rate)
 ), not_in AS (
     SELECT freight_rate_id FROM freight_rate
     MINUS
     SELECT ids from unique_ids
 )
 select freight_rate_id from not_in
)

Note the use of the WITH clause to come up with a list of ID’s to delete. This was done because of a constraint in place that only allows us to use 5 seconds of CPU time on the database server for any single query. A query similar to the following wouldn’t work:

DELETE FROM freight_rate
WHERE ROWID NOT IN(SELECT first_value(ROWID) OVER(PARTITION BY
                                   CARRIER_EQUIP_TYPE_XREF_ID,
                                   CARRIER_LANE_ID,
                                   RATE_ADJ_ID,
                                   CURRENCY_UOM_ID,
                                   EFF_END_DTE
                      ORDER BY rate_eff_strt_dte DESC)
                   FROM freight_rate);

This query would run for >2min before failing on the 5s CPU limit. The first query runs in just a couple of seconds!