Delete all duplicate rows except for one in MySql having no primary key.

Someone asked me to delete all rows from a table except one entry with a single query.
This is not so easy for me :), Anyway after work and research I have found the solution, I am sharing with you How you can do the same.

Let’s take an example of table tbl_employee have duplicate names data as follows

SELECT * FROM tbl_employee;
——————–
id    name
——————–
1     mahesh
2     suresh
3     john
4     john
5     john
6     mahesh
——————–

If you want to keep the row with the lowest id value then

DELETE e1 FROM tbl_employee e1, tbl_employee e2 WHERE e1.id > e2.id AND e1.name = e2.name

Or with highest id value

DELETE e1 FROM tbl_employee e1, tbl_employee e2 WHERE e1.id < e2.id AND e1.name = e2.name