Deletion of duplicate records using one query only

I am using SQL server 2005.

I have a table like this -

ID    Name
1      a
1      a
1      a
2      b
2      b
3      c
4      d
4      d

In this, I want to delete all duplicate entries and retain only one instance as -

ID     Name
1       a
2       b
3       c
4       d

I can do this easily by adding another identity column to this table and having unique numbers in it and then deleting the duplicate reco开发者_如何学编程rds. However I want to know if I can delete the duplicate records without adding that additional column to this table.

Additionally if this can be done using only one query statement. i.e. Without using Stored procedures or temp tables.

Using a ROW_NUMBER in a CTE allows you to delete duplicate values while retaining unique rows.

WITH q AS (
  SELECT RN = ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID )
         , ID
         , Name
  FROM   ATable
)
DELETE FROM q WHERE RN > 1

Lieven is Right... however you may want to tweak lieven's code by just adding a top clause in the delete statement like this:

delete top(1) from q where RN > 1;

Hope this helps

You may use this query:

delete a from
(select id,name, ROW_NUMBER() over (partition by id,name order by id) row_Count
from dup_table) a
where  a.row_Count >1

delete from table1
USING table1, table1 as vtable
WHERE (NOT table1.ID=vtable.ID)
AND (table1.Name=vtable.Name)

DELETE FROM tbl
WHERE ID NOT IN (
    SELECT MIN(ID)
    FROM tbl
    GROUP BY Name
)