Geek asks and answers

Sql query to remove only one duplicate row

I have a table with several duplicate rows. I want to remove only one duplicate row.

For example, repeating lines I'v 9 should therefore only delete one line and display the 8 remaining lines.

Example

call date 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

only one line should be deleted from the previous date and 3 lines should be displayed

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

top date should delete only one line and display 2 lines

How can i do this?

+5
source share
6 answers

This solution allows you to remove one row from each set of duplicates (and not just process one 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;

, [date], [timestamp] ...

+6

, MySQL :

DELETE TOP (numberOfRowsToDelete) FROM db.tablename WHERE {condition for ex id = 5};
+3

?

? ? ? ?

, TOP, :

Delete from [tablename] where id in (select top 1 id from [tablename] where [clause])
+1

SQL Server 2005+ :

;WITH CTE AS
(
    SELECT  *, 
            ROW_NUMBER() OVER(PARTITION BY [date], calling, called, duration, [timestamp] ORDER BY 1) RN
    FROM YourTable
)
DELETE FROM CTE
WHERE RN = 2
+1

, :

  • min (rownumber)

Edit:

The ruble number is in the internal request and will increase the number of roubers in all rows. In an external query, I make a group according to an internal query and select min (rownumber) for each group. Since each group consists of repeating rows, I remove min (rownumber) for each group.

0
source

using LIMIT 1will help you remove only 1 ROWthat matches the query DELETE:

DELETE FROM `table_name` WHERE `column_name`='value' LIMIT 1;

before :

+----------------------+
| id  |  column_name   |
+-----+----------------+
| 1   |  value         |
+-----+----------------+
| 2   |  value         |
+-----+----------------+
| 3   |  value         |
+-----+----------------+
| 4   |  value         |
+-----+----------------+

after

+----------------------+
| id  |  column_name   |
+-----+----------------+
| 1   |  value         |
+-----+----------------+
| 2   |  value         |
+-----+----------------+
| 3   |  value         |
+-----+----------------+
0
source

More articles:


All Articles