delete all duplicated rows in sql

Question

I have table like this

|-------------------------|
|   A   |   B   |    C    |
|-------------------------|
|   1   |   2   |    5    |
|-------------------------|
|   1   |   2   |    10   |
|-------------------------|
|   1   |   2   |    2    |
|-------------------------|

I need to delete all duplicated rows with equals A nad B value and lower C value

after running sql script i need to have only this row with top C Value for every equals A and B columns

|-------------------------|
|   A   |   B   |    V    |
|-------------------------|
|   1   |   2   |    10   |
|-------------------------|

Answer 1

One method is window functions:

select t.*
from (select t.*, 
             row_number() over (partition by a, b order by v desc) as seqnum
      from t
     ) t
where seqnum = 1;

This returns the entire row, which can be handy if you want additional columns. If you really need just the three columns, then aggregation does what you want:

select a, b, max(v)
from t
group by a, b;

In standard SQL, you can keep only the maximum value using:

delete from t
    where t.v < (select max(t2.v) from t t2 where t2.a = t.a and t2.b = t.b);