Monday, August 16, 2010

Remove duplicate rows

Many a times when we update database, there are chances that we may insert duplicates. It is a headache to maintain such database if the source is not within our control.

Below is a way to solve the headache.

with Uniqrow as (

select *,ROW_NUMBER() over (partition by Case_ID order by case_id,x_Cust_Track_No DESC) as rownumb FROM [wfm_ods].[dbo].[TABLE_CASE]

)

delete from Uniqrow where rownumb > 1

The above is based on table_case in WFMODS database. It somehow retrieved duplicate records and there is no way to find out why. Its function is to simply based on the grouping of case_id and rank it. Since we can't just do a delete directly, we use a CTE "with clause" to encase the ranking statement.

The result is very fast. removing 2700 case in less than 1 sec from a total of 11000 cases.

No comments:

Post a Comment