Basics - Deleting Duplicate Entries using ROW_NUMBER() Function

ROW_NUMBER() - Divides the result set produced by the FROM clause into partitions to which the ROW_NUMBER function is applied (As mentioned in MSDN)

IF EXISTS (SELECT 1 FROM SYS.TABLES WHERE NAME ='TEST')

DROP TABLE TEST
GO
--STEP1
CREATE TABLE TEST
(ID INT,
NAME VARCHAR(20))
GO

--STEP 2
INSERT INTO TEST (ID,NAME)
VALUES (1,'Raj')
GO
INSERT INTO TEST (ID,NAME)
VALUES (1,'Raj')
GO
INSERT INTO TEST (ID,NAME)
VALUES (2,'Ram')
GO
INSERT INTO TEST (ID,NAME)
VALUES (2,'Ram')
GO
SELECT * FROM TEST
GO

--STEP 3
WITH [CTE DUPLICATE] AS
(
SELECT
RN = ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID DESC),
Id,NAME
FROM TEST
)
DELETE FROM [CTE DUPLICATE]
OUTPUT DELETED.*
WHERE RN > 1;
GO

--STEP 4
SELECT * FROM TEST