mysql – SQL query to remove duplicates from table by one field

Question:

There is a table with duplicates in MySQL: (by name field)

id |  name | surname
--------------------
1  | niko  | surname
2  | niko  | surname
3  | jane  | surname
4  | jane  | surname
5  | ivan  | surname

You need to get a table like this

id |  name | surname
--------------------
1  | niko  | surname
3  | jane  | surname
5  | ivan  | surname

Answer:

Query for the resulting table:

SELECT min(id), name, surname
FROM `table`
GROUP BY name, surname

To remove duplicates, the following technique is suitable:

CREATE TEMPORARY TABLE `t_temp` 
as  (
   SELECT min(id) as id
   FROM `table`
   GROUP BY name, surname
);

DELETE from `table`
WHERE `table`.id not in (
   SELECT id FROM t_temp
);

Of course there are nuances…