CODEWITHSUNDEEP
sqlsql-serverduplicates
Mega
Mega

Reputation: 565

SQL Delete duplicate rows with lowest number

I can't find appropriate way to delete duplicate keys in sql table with lowest number. If there is a duplicate rows with the same Number, I need to delete one of them.

For example

Key     Number  Description

11111   5   Desc1
11111   4   Desc2
22222   2   Desc1
22222   2   Desc2
33333   3   Desc1
33333   5   Desc2

Here I need to be deleted the second row with Number 4 which is smaller then Number 5, one of the third or fourth row, and fifth row which have smaller Number 3 then the last row 5.

Upvotes: 5

Views: 4526

Answers (2)

wildplasser
wildplasser

Reputation: 44250

DELETE FROM ztable dd
WHERE EXISTS (
  SELECT * FROM ztable ex
  WHERE ex.zkey = dd.zkey
  AND (ex.znumber > dd.znumber
       OR (ex.znumber = dd.znumber AND ex.description > dd.description)
       )
  );

Note: I renamed key and number to zkey and znumber to avoid confusion with reserved words/ keywords. Similar for ztable.

Upvotes: 3

TechDo
TechDo

Reputation: 18659

Query to remove duplicate in SQL-Server:

;with c as
(
    select *, row_number() over(partition by [Key] order by Number desc) as n
    from YouTable
)
delete from c
where n > 1

Upvotes: 6

Related Questions