删除基于复合键的重复行并保留最新的行条目mysql php

Question

I have a mysql table "orders" contains 3 fields id, user_id and order_id

I want to remove the row having user_id and order_id same, and the row with greater id would not be deleted which is id = 3 in this scenario. So the result would be looking like this,

Answer 1

First duplicate record should be inserted and then delete the duplicate record.

DELETE n1 FROM orders n1, orders n2 WHERE n1.id < n2.id AND n1.user_id = n2.user_id AND n1.order_id = n2.order_id;