we can use cursor.

Hope this methodology would help you better

1.Create a temp table
2.Select duplicated row's empid,empname into the temp table.
3.Create a cursor by selecting values from temp table.
4.Keep either min or max(empid) from original table and 
delete the rest of the duplicated rows.

Table structure :-

empid   empname
1       bala
2       bala
3       bala
4       bala
5       arun
6       arun
7       arun       
8       ram
9       ram

Delete from employee where empid 
not in (Select min(empid) from employee group by emp
having count(empid)>1)

By
Kumar

Kumar your query do not solve the purpose accurately.

It should be as

Delete from employee where empid 
not in (Select min(empid) from employee group by empname
having count(empname)>=1)

use distinct commond

distinct(ename) from table name

WITH DUPLICATE(EmpName, RowNumber)
AS
(SELECT EmpName,ROW_NUMBER() OVER (PARTITION BY EmpName 
order by EmpName) AS RowNumber
FROM Employee)

DELETE FROM DUPLICATE WHERE RowNumber > 1

------------------------------------------------------------
Using CTE (Common Table Expressions) and ROW_NUMBER as 
ranking fucntion 


Jiri JANECEK
MSE, MBA, MCSD.NET

delete from emp where empid not in(select max(empid) from 
emp group by empname having count(*)>=1)