I have a table that is supposed to have to unique keys. I generate one key (Col2) based on the uniqueness of the other key (Col1). Now, something is happened, I have unique keys in Col2, but some data is repeated in Col1. For instanse, we have
and I want to automatically delete row with value of DB02, and DB01 remains. Because 02 is bigger than 01 and I want to keep the lowest value (despite the fact that it includes characters).
Could you please help me with the query?
Here is the picture of the query I tried, and the results I did not get. :(
The answer is similar to what Giorgos Betsos was suggested, but the code should be like this:
WITH ToDelete AS ( SELECT Col2 ,ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY Col1) AS rn FROM mytable ) DELETE FROM ToDelete WHERE rn > 1