CODEWITHSUNDEEP
mysqlsql
Puhal
Puhal

Reputation: 52

Deleting the duplicate records, by comparing it with a max value in another column - mysql

id  | rem_id |max_val
--  | ------ |------
1   | 1      | 7
2   | 2      | 6
3   | 3      | 1
4   | 1      | 1
5   | 2      | 1
6   | 3      | 1

In the above table I need to remove the duplicates from the rem_id column with min val in the max_val column

id  | rem_id |max_val
--  | ------ |------
1   | 1      | 7
2   | 2      | 6
3   | 3      | 1

Upvotes: 1

Views: 39

Answers (1)

SqlZim
SqlZim

Reputation: 38023

This will delete all but the highest valued max_val for each rem_id, or multiples of the same max_val by deleting those with a higher id:

delete t
from t 
  left join t as i
    on i.rem_id = t.rem_id
    and (i.max_val > t.max_val
      or (i.max_val = t.max_val and i.id < t.id)
      )
where i.id is not null;

rextester demo: http://rextester.com/QKIK17666

returns: 

+----+--------+---------+
| id | rem_id | max_val |
+----+--------+---------+
|  1 |      1 |       7 |
|  2 |      2 |       6 |
|  3 |      3 |       1 |
+----+--------+---------+

Upvotes: 1

Related Questions