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?
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...
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With