SQL: Delete Duplicates

To identify the duplicates

select USER_ID, industry_id, COUNT(*) times from user_industry group by user_id, industry_id

having COUNT(*)>1

 

To query the rows to be deleted

 

select * from

(

   select *, rn=row_number() over (partition by user_id, industry_id order by user_id, industry_id)

   from user_industry

 )  x

 

To delete the duplicates

 

delete x from (

  select *, rn=row_number() over (partition by user_id, industry_id order by user_id, industry_id)

  from user_industry

) x

where rn > 1;