Delete duplicate record SQL Server

Question

Please consider the scenario below

I have a table like below

Tag | Id | Client | ....and more columns
c     30    X
c     40    Y
c     50    X
c     60    A
c     30    B 
c     40    C
d     50    D
d     70    E 
d     80    X
d     90    Z
i     30    X
i     90    Z
i    100    X
i     40    M

I want to select records from table in such way that if tag=i the row below gets removed from resultset

  i     30    X
  i     90    Z

This is because the row with id=90 have already appeared with tag=d and client=Z. But the row

i     40    M

must not be deleted even though id=40 has already appeared with client=C because client column value are different.

DELETE FROM myTable
WHERE tag=i AND id IN( SELECT id FROM myTable t1
                      INNER JOIN myTable t2 
                      ON t1.id=t2.id 
                      WHERE tag=d or tag=c )

Answer 1

You can use following CTE with ROW_NUMBER to detect and delete duplicates according to your rule:

WITH CTE AS 
(
  SELECT [Tag], [Id], [Client],
    RN=ROW_NUMBER()OVER(PARTITION BY [Id], [Client] ORDER BY [Tag])
  FROM dbo.Tags
)
DELETE FROM CTE 
WHERE RN > 1
AND [Tag] = @Tag;

DEMO

Deletes these records:

TAG   ID    CLIENT   RN
i     30      X      2
i     90      Z      2

Over Clause