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

https://stackoverflow.com/questions/14953624/how-to-delete-records-not-in — xQbert, Feb 11 '21 at 13:15

Gordon Linoff · Accepted Answer · 2021-02-11T13:20:18.057

3

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);

edited Feb 11 '21 at 13:20

answered Feb 11 '21 at 12:07

Gordon Linoff

1,242,037
58
646
786

1

ok with second script i can find the max value, but how to delete all lower rows – Svetoslav Panteleev Feb 11 '21 at 12:27

delete all duplicated rows in sql

1 Answers1