Delete Duplicate Rows Using CTE

CREATE TABLE Employee(EmployeeID INT,EmployeeName VARCHAR(50),

Designation VARCHAR(50))

 INSERT INTO Employee(EmployeeID,EmployeeName,Designation,Department)
VALUES(1,'VENKAT','SUPERVISOR','PHARMACY'),
VALUES(2,'VENKAT','SUPERVISOR','PHARMACY'),
VALUES(3,'RAM','SALES','GROCERY'),

VALUES(4,'RAM','SALES','GROCERY'),

VALUES(5,'RAM','SALES','GROCERY')

 WITH emp AS (SELECT ROW_NUMBER ( ) OVER ( PARTITION BY EmployeeName,  Designation ORDER BY EmployeeID )

AS RNUM FROM Employee )

DELETE FROM emp WHERE RNUM > 1



--Delete duplicate rows with more than 1 column



CREATE TABLE [dbo].[tbl_Test]([ID] [int] IDENTITY(1,1) NOT NULL,
[Col1] [nvarchar](255) NULL,[Col2] [nvarchar](255) NULL,[Col3] [nvarchar](50) NULL,
CONSTRAINT [PK_tbl_Test] PRIMARY KEY CLUSTERED([ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY])
ON [PRIMARY]


INSERT INTO tbl_Test (Col1, Col2, Col3) VALUES ('1','2','3')
INSERT INTO tbl_Test (Col1, Col2, Col3) VALUES ('1','2','4')
INSERT INTO tbl_Test (Col1, Col2, Col3) VALUES ('1','2','5')
INSERT INTO tbl_Test (Col1, Col2, Col3) VALUES ('8','2','6')
INSERT INTO tbl_Test (Col1, Col2, Col3) VALUES ('1','9','7')


;With CTE as

(Select *,row_number() over (partition by Col1,Col2 order by Col1,col2 Desc) as row1from [tbl_Test])

Delete from CTE where row1!=1