[Solved] How do I delete duplicate rows and keep the row with higher value? [closed]

Question

delete t1 
from some_table t1
join some_table t2
  on  t2.a3 = t1.a3
  and t2.a4 > t1.a4;

Demo: http://rextester.com/RERGD32491

This will only keep rows with the highest value in A4 per A3. It’s possible that two rows with the same value in A3 have the same highest value in A4. Since you didn’t specify what to do in that case – Both rows will be kept.

Accepted Answer

delete t1 
from some_table t1
join some_table t2
  on  t2.a3 = t1.a3
  and t2.a4 > t1.a4;

Demo: http://rextester.com/RERGD32491

This will only keep rows with the highest value in A4 per A3. It’s possible that two rows with the same value in A3 have the same highest value in A4. Since you didn’t specify what to do in that case – Both rows will be kept.