How to find and remove duplicates from a table?

we can use max and subquery or row_number windown function

Table Name: Employee S.No Name Gender Salary 1 Ram Male 60000 1 Ram Male 60000 2 Gita Female 55000 3 Shyam Male 75000 1 Ram Male 60000 3 Shyam Male 75000 2 Gita Female 55000 With EmployeeCTE AS ( select *, Row_Number() over (partition by Id Order by Id) As RowNumber from Employee) Delete from EmployeeCTE where RowNumber >1

We can remove duplicates using a Ranking function Row_Number() with partition by clause. To learn more about Windows Ranking Function, you can also visit CREATE TABLE TEST_WF(ID INT, NAME VARCHAR(5)) INSERT INTO TEST_WF VALUES (1,'A'),(2,'B'),(3,'B'),(4,'C'),(5,'C'),(6,'C'),(7,'D') SELECT * INTO #TEMP FROM TEST_WF ;WITH CTE AS (SELECT NAME, ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY NAME) AS RN FROM #TEMP) DELETE FROM CTE WHERE RN > 1