SQL Delete duplicate rows with the smallest number

Question

I cannot find a suitable way to remove duplicate keys in sql table with the smallest number. If there are duplicate rows with the same number, I need to delete one of them.

for instance

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 delete the second line with number 4, which is less than number 5, one of the third or fourth line and the fifth line, which have a lower number 3, and then the last line 5.

Answer 1

Request to delete 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 

Answer 2

 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 the key and number to zkey and znumber to avoid confusion with reserved words / keywords. Similarly for ztable.