Delete IDs that repeat more then once, but leave the first occurrence

Question

I have a table and need to delete entire row where ID occurs second and subsequent times, but leave the first occurrence of suCustomerIDBy the way. M table has ID which is a primary key and CustometID which is duplicated. So I need to remove all rows with duplicated CustomerID.

Delete From Table1 where ID IN (select ID From Table1 where count(distinct CutomerID) >=2 group by CustomerID)

The code above will delete all id including the first occurrence of each of the IDs, but I need to keep their first occurrence. Please advise.

Do you have another column that is unique? – juergen d May 13 '16 at 21:54 — juergen d, May 13 '16 at 21:54
How do you know which occurance is first? – Malk May 13 '16 at 22:03 — Malk, May 13 '16 at 22:03
Yes, there is a row_id which is a primery key – Data Engineer May 13 '16 at 22:52 — Data Engineer, May 13 '16 at 22:52

score 2 · Accepted Answer · answered May 13 '16 at 22:57

2

This code should give you what you need.

There may be better ways to do it if you can provide the full table schema for Table1

If you obtain the row number and then just ignore the first ones:

;WITH cte 
AS 
(
  SELECT ID, 
     ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) AS Rn
  FROM [Table1]
)
DELETE cte WHERE Rn > 1

answered May 13 '16 at 22:57

Uberzen1

415
6
18

Thank you. I'll try to adjyst it to my particular need and will accept your answer – Data Engineer May 13 '16 at 23:07

Vigya · Answer 2 · 2016-06-14T07:40:28.783

0

delete a from(
Select dense_rank() OVER(PARTITION BY id ORDER BY id) AS Rn,*
from Table1)a
where a.Rn>1

edited Jun 14 '16 at 07:40

answered May 13 '16 at 23:07

Vigya

122
1
7

Although this code may answer the question, providing additional context regarding _why_ and/or _how_ it answers the question would significantly improve its long-term value. Please [edit] your answer to add some explanation. – Toby Speight Jun 14 '16 at 15:29

Delete IDs that repeat more then once, but leave the first occurrence

2 Answers2