remove repeated data in mysql

Question

I have the following table called cadastro: Cod | name | age

With the data:

   COD   NOME     IDADE 
    2  | rafael  | 15
    3  | bruno   | 17
    4  | rafael  | 33
    5  | sabrina | 18
    6  | bruno   | 15
    7  | paulo   | 15
    8  | rafael  | 15

Note that some names repeat themselves, I need to run a command on my mysql to delete all repeated names and keep only 1, which contains the largest cod.

Example:

I have 3 records with the name raphael.

Then I’d just have to keep the 8 | rafael | 15.

This is possible to be done?

Answer 1

You can make a DELETE with INNER JOIN in the same table, however in INNER JOIN you will bring only the records where the cod is less than in the first table.

DELETE b FROM cadastro a
INNER JOIN cadastro b ON a.nome = b.nome AND b.cod < a.cod

Backup the table before running, I don’t have your table here to take the test.