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