sql query to delete only one duplicate row

sql-server

Solution

This solution allows you to delete one row from each set of duplicates (rather than just handling a single block of duplicates at a time):

;WITH x AS 
(
  SELECT [date], rn = ROW_NUMBER() OVER (PARTITION BY 
    [date], calling, called, duration, [timestamp]
    ORDER BY [date])
  FROM dbo.UnspecifiedTableName
)
DELETE x WHERE rn = 2;

As an aside, both `[date]` and `[timestamp]` are terrible choices for column names...

Problem

I've a table with some duplicate rows in it. I want to delete only one duplicate row. For example I'v 9 duplicate rows so should delete only one row and should show 8 remaining rows. example date calling called duration timestampp ``` 2012-06-19 10:22:45.000 165 218 155 1.9 121 2012-06-19 10:22:45.000 165 218 155 1.9 121 2012-06-19 10:22:45.000 165 218 155 1.9 121 2012-06-19 10:22:45.000 165 218 155 1.9 121 ``` from above date should delete only one row and should show 3 rows ``` 2012-06-19 10:22:45.000 165 218 155 1.9 100 2012-06-19 10:22:45.000 165 218 155 1.9 100 2012-06-19 10:22:45.000 165 218 155 1.9 100 ``` from above date should delete only one row and should show 2 rows How can I do this?

Original source