Delete duplicate rows

SET ROWCOUNT 1
delete emp from emp a where (select count(*) from emp b where a.name=b.name) >1
WHILE @@rowcount > 0
delete emp from emp a where (select count(*) from emp b where a.name=b.name) >1
SET ROWCOUNT 0

or


DELETE
FROM MyTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM MyTable
GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3)

or



WITH empTable as
(
SELECT ROW_NUMBER() Over(PARTITION BY EmpName,Position ORDER BY EmpName) As RowNumber,* FROM emp
)
DELETE FROM  empTable  where RowNumber >1
SELECT * FROM emp order by Id asc