yousef yegane yousef yegane - 5 months ago 19
SQL Question

delete rows with duplicate Col1, with consideration of deleting the rows with highest value in Col2

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

enter image description here

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

enter image description here

Answer

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