Delete Duplicate rows from table which have same Id

Question

I have a table Emp which have records like this

Id     Name
1      A
2      B
3      C
1      A
1      A
2      B
3      C

Now I want to delete the duplicate rows from the table I am using this query to select or count number of duplicate records

SELECT NameCol, COUNT(*) as TotalCount FROM TestTable 
GROUP BY NameCol HAVING COUNT(*) > 1 
ORDER BY COUNT(*) DESC

and what query should i write to delete the duplicate rows from table.

if I write this query to delete the duplicate records then it is giving a (0) row Affected result.

`DELETE FROM TestTable 
    WHERE ID NOT IN ( SELECT MAX(ID) FROM 
                                     TestTable 
                                     GROUP BY NameCol
                    )`

Solution

For sqlserver 2005+

Testdata:

declare @t table(Id int, Name char(1))
insert @t values
(1,'A'),(2,'B'),(3,'C'),(1,'A'),(1,'A'),(2,'B'),(3,'C')

Delete statement(replace @t with your Emp table)

;with a as
(
select row_number() over (partition by id, name order by id) rn
from @t
)
delete from a where rn > 1

select * from @t

Comments

[t-clausen.dk]

possible duplicate of How can I remove duplicate rows?

[Peter B]

ROWID is Oracle-only, while this question is about Sql Server - see the tags.