Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

sql query to delete only one duplicate row

Tags:

sql-server

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?

like image 204
user1469706 Avatar asked Jun 20 '12 15:06

user1469706


1 Answers

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

like image 91
Aaron Bertrand Avatar answered Sep 24 '22 00:09

Aaron Bertrand