mysql - Removing duplicate rows using delete query?

Question

This question was asked for an interview, I still cant find an answer by googling it.

I have a table with two columns. Table doesn't have any primary key. I want to delete the duplicate rows. I can do it by alter table but they want a delete query.

Here is the table data.

empid name
1     rahul
2     rajeev
1     rahul
2     rajeev
2     rajeev
3     elias
4     amith
2     rajeev

The result should be be

1     rahul
2     rajeev
3     elias
4     amith

Answer 1

As @Marc B says in the comments, this sounds like a trick question. But if it's not...

The only solution I know of (without being aware of a proper ranking function in mysql) is to somehow loop through each name and running the following query:

DELETE FROM tbl_name
WHERE name=@name
LIMIT 1

This wouldn't be one query so it wouldn't be a valid answer but I'm not sure of any other way to do it in mysql.

Another option would be to use SELECT DISTINCT into a temp table, TRUNCATE the original table, and INSERT the records back into it from a temp table. Again, this doesn't satisfy the requirements.