01/22

 

SQl Server .

 

Remove duplicate row from a datatable with CTE function

 

With CTE( col1,col2….., DuplicateCount)

as

(

select col1,col2,…. Row-NUMBER()

OVER(PARTITION BY col1,col2,…. ORDER BY Model ) AS DuplicateCount
    FROM tablename
    )

)

 

DELETE
    FROM CTE
    WHERE DuplicateCount > 1

 

Example

WITH CTE (Model,GroupNo,CompCode,Vari,Ser ,[Name],ATT, DuplicateCount)
    AS
    (
    SELECT Model,GroupNo,CompCode,Vari,Ser ,[Name],ATT,
    ROW_NUMBER() OVER(PARTITION BY Model,GroupNo,CompCode,Vari,Ser ,[Name],ATT ORDER BY Model ) AS DuplicateCount
    FROM dbo.ac_History_Tiem_Name
    )
    DELETE
    FROM CTE
    WHERE DuplicateCount > 1

 

 

With CTE( col1,col2….., DuplicateCount)

as

(

select col1,col2,…. Row-NUMBER()

OVER(PARTITION BY col1,col2,…. ORDER BY Model ) AS DuplicateCount
    FROM tablename
    )

)

 

DELETE
    FROM CTE
    WHERE DuplicateCount > 1