SQL Server – How to remove duplicate rows

IF OBJECT_ID(‘EmployeeDetails’) IS NOT NULL
DROP TABLE EmployeeDetails
CREATE TABLE [dbo].[EmployeeDetails]
(
[Employee] [varchar](10) NULL,
[JoiningDate] [datetime] NULL,
[DeptID] [int] NULL
)
GO
INSERT INTO EmployeeDetails(Employee, JoiningDate, DeptID)
SELECT ‘Gorav’,’1919-03-18 ‘,1008
UNION ALL
SELECT ‘Maneesh’,’1927-03-18 ‘,91
UNION ALL
SELECT ‘Anant’,’1927-04-01 ‘,139
UNION ALL
SELECT ‘Gorav’,’1919-03-18 ‘,1008
UNION ALL
SELECT ‘Maneesh’,’1927-03-25 ‘,92
UNION ALL
SELECT ‘Anant’,’1927-03-25 ‘,108
UNION ALL
SELECT ‘Gorav’,’1919-04-01 ‘,150
UNION ALL
SELECT ‘Maneesh’,’1927-04-01 ‘, 123
UNION ALL
SELECT ‘Anant’,’1927-04-01 ‘, 139
UNION ALL
SELECT ‘Gorav’,’1919-04-08 ‘, 168
– query to check duplicate rows
SELECT Employee, JoiningDate, DeptID,Ranking = row_number() OVER(PARTITION BY Employee, JoiningDate, DeptID ORDER BY NEWID() ASC)
FROM EmployeeDetails
– query to delete duplicate rows
WITH RemoveDuplicate(Employee, JoiningDate, DeptID, Ranking)
AS(
SELECT Employee, JoiningDate, DeptID,Ranking = row_number() OVER(PARTITION BY Employee, JoiningDate, DeptID ORDER BY NEWID() ASC)
FROM EmployeeDetails
)
DELETE FROM RemoveDuplicate WHERE Ranking > 1