Deletion of duplicate records using one query only

Question

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 records. 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.

Answer 1

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

Answer 2

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