DELETE FROM A
WHERE     (id NOT IN
                          (SELECT     MAX(ID)
                            FROM          A
                            GROUP BY name))

suppose i have table called A. A has two coloumn id
(identity) as int and Name as nvarchar. I have data like 
this.

id Name
1  C
2  D
3  C
4  E
5  D
6  D

There is a table like this: tablename: example


Name                        Age
chandran                     23
ranjith                      24
chandran                     23

In this table the name:chandran and age:23 are the 
duplicate records  .so we need to delete this using this 
sql statements


delete from example group by name,age having count>1

Mr. Chandran, I tried your query but the system is throwing 
error message  as 'Incorrect syntax near the 
keyword 'group''.

SELECT distinct column_names INTO temp_table FROM main_table
drop table main_table
sp_rename temp_table,main_table

Hi friends, please just try out this. This works fine for me.

We have lot of methods to do this. But using temp table,
drop the original table,retain the temp as orinial is not a
good pratice.

When u have large no of data it will affect ur performance.


DELETE FROM employee WHERE((SELECT eid,COUNT(eid) FROM
employee GROUP BY eid) > 1)

Name                        Age
chandran                     23
ranjith                      24
chandran                     23

To delete one of the duplicate records use following query 

(For sql server 2000)

Set rowcount 1
delete from [tableName] order by name 
set rowcount 0
--Write a cursor to delete multiple duplicate records 
Or (In sql server 2005)

;with DelDup as (select row_number() over (partition by 
sname order by sname) as RONO ,sname from [TableName]) 
Delete from DelDup where RONO > 1

Table Name: Example

Name                        Age
chandran                     23
ranjith                      24
chandran                     23

To delete one of the duplicate records use following query 


delete from example where age in(select age from example 
group by age having count>1)

This will help u a lot bcz dynamically it will delete 
duplicates



create table tbl1    (col1 int)





insert into tbl1 values(1)
insert into tbl1 values(1)
insert into tbl1 values(1)
insert into tbl1 values(2)
insert into tbl1 values(2)
insert into tbl1 values(2)
insert into tbl1 values(2)
insert into tbl1 values(3)
insert into tbl1 values(3)


select * from tb1


set rowcount 1
select 'start'

while @@rowcount > 0 
delete a from tbl1 a where (select count(*) from tbl1 b 
where a.col1 = b.col1)>1
set rowcount 0
select * from tbl1
set nocount off

delete from tab_nam where

Create table info(comp_id int identity(101,1),comp_name 
varchar(50))
insert into info values('Progressive Ltd.')
insert into info values('Progressive Ltd.')
insert into info values('EliResearch')
insert into info values('Patni')
insert into info values('Accenture')
insert into info values('Accenture')
select * from info

DELETE FROM info 
WHERE comp_name IN
(SELECT comp_name FROM info 
GROUP BY comp_name HAVING COUNT(comp_name) > 1)

WITH INTER_TABLE(ROW, COL1) AS( 
select row_number() over (partition by 
COL1 order by COL1) as ROW ,COL1 from [TABLE_NAME]
)
Delete from INTER_TABLE where ROW > 1

your table look like this and want to delete duplicate 
record

chandran                     23
ranjith                      24
chandran                     23


delete top(1) from tablename where name='chandran' and 
age=23

Delete From Tablename where(ID Not in (Select max(ID) from 
Tablename Group by name))

Tablename :Friend

ID    Name   Age    city
101   vinod    22    Gwalior
102   Pritesh  23    Gwalior
102   Pritesh  23    Gwalior
103   Arvind   24    Gwalior


Here Id-102 is repeated so friend if u want to delete this 
duplicate raw  Try Above code in Sql-sever

This query delete duplicate records(but not delete both
duplicate)
set rowcount 1
delete yourtable
from yourtable a
where (select count(*) from yourtable b where b.name=a.name
and b.age=a.age)>1
while @@rowcount >0
delete yourtable
from yourtable a
where(select count(*) from yourtable b b.name=a.name and
b.age=a.age)>1
set rowcount 0

I have The Same Problem And I Have Done Woth This 


DECLARE @empid int, @empname varchar(50),@Cnt int

DECLARE duplicate_cursor CURSOR FOR

-- select all columns in table bit you must have an count column
select empid,empname, count(*) Cnt
from tbl_Temp
group by empid, empname
Having count(*) > 1

OPEN duplicate_cursor

FETCH NEXT FROM duplicate_cursor
INTO @empid, @empname,@Cnt

WHILE @@FETCH_STATUS = 0
BEGIN

SET @Cnt = @Cnt - 1

SET ROWCOUNT @Cnt

DELETE tbl_Temp
WHERE @empid = empid AND @empname = empname 


FETCH NEXT FROM duplicate_cursor
INTO @empid, @empname
END

CLOSE duplicate_cursor
DEALLOCATE duplicate_cursor

-- dont forget to set rowcount to 0
SET ROWCOUNT 0

select * from dbo.duplicatetest
set rowCount 1
delete from dbo.duplicatetest where iD=1;
set rowCount 0

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

using the following query u can delete the duplicate records.
but it is available only on SQL Server 2005.

with mytable as                                     
(                                      
 select *, ROW_NUMBER() OVER(partition by id,myname order by
id desc) as RowNumber from test                          
)
delete from mytable where rownumber>1


"Have a Great Day"

Bala

I have a query that I use with success I have a table with 
telephone numbers in it and sometimes I have duplicate 
phone numbers in the table... here is how I see what they 
are and then remove them. 

===========================================

select telephone 
from Table_A
group by telephone having count(*) > 1

SET ROWCOUNT 1
SELECT NULL
WHILE @@rowcount > 0
DELETE step
FROM Table_A as step
INNER JOIN
(SELECT telephone
FROM Table_A
GROUP BY telephone HAVING count(*) > 1)
AS t ON t.telephone = step.telephone
SET ROWCOUNT 0

===========================================

This query will actually find and remove the duplicates 
from Table_A but will not remove both instances it will 
only remove one... leaving you with one good record... hope 
this helps someone.   : )