delete  tblname where columname  in (select columnname 
FROM  tblname  GROUP BY  columnname having count(columnname)
>=2)

We can delete duplicate rows from table by using distinct 
command.

delete from my_table where my_primary_key in
  (select a.primary_key from my_table a, my_table b
     where not a.my_primary_key = b.my_primary_key
       and [insert restriction that makes the 2 rows the same]
   )

this can help if you want to keep only different records

create table test
(
 id int,
 name varchar(20)
)

insert into test VALUES(1,'test')
insert into test VALUES(2,'test')
insert into test VALUES(2,'test')
insert into test VALUES(3,'test')
insert into test VALUES(4,'test')
insert into test VALUES(5,'test')
insert into test VALUES(6,'test')
insert into test VALUES(7,'test')
insert into test VALUES(7,'test')
insert into test VALUES(7,'test')



select * from test order by 1

while @@rowcount != 0
begin
delete top (1) test where id  in 
(
    select id 
    FROM  test  
	GROUP BY  id having count(id)>1
)
end

select * from test order by 1


-- At the end you will have only differents IDS

CREATE TABLE dbo.duplicateTest      ------Deleting
Duplicates with same id 
( 
[ID] [int] , 
[FirstName] [varchar](25), 
[LastName] [varchar](25)  
) ON [PRIMARY] 

INSERT INTO dbo.duplicateTest VALUES(1, 'Bob','Smith') 
INSERT INTO dbo.duplicateTest VALUES(2, 'Dave','Jones') 
INSERT INTO dbo.duplicateTest VALUES(3, 'Karen','White') 
INSERT INTO dbo.duplicateTest VALUES(1, 'Bob','Smith') 



SELECT * FROM dbo.duplicateTest 

SET ROWCOUNT 1 
DELETE FROM dbo.duplicateTest WHERE ID = 1 
SET ROWCOUNT 0 

SELECT * FROM dbo.duplicateTest 

Drop table dbo.duplicatetest

;with empctc(empid,ename,sal,deptid,ranking)
as
(Select empid,ename,sal,deptid,ranking=Dense_rank() over (
partition by empid,ename,sal,deptid order by NEWID() asc) 
from emp
)
delete * from empctc where ranking>1

while @@rowcount != 0
begin
delete top (1) test where columnname  in 
(
    select columnname
    FROM  tablename 
	GROUP BY  columnname having count(*)>1
)
end

delete from table_name where column_name='value' and rowid 
not in (select max(rowid) from table_name where 
column_name='value');

e.g. create table duplicate (name varchar(15), rollno number
(10));

insert into duplicate (name,rollno) values ('mkumar',2);
insert into duplicate (name,rollno) values ('mkumar',2);

delete from duplicate where name='mkumar' and rowid not in 
(select max(rowid) from duplicate where name='mkumar');