sql query to delete only one duplicate row

Question

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?

Answer 1

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