(my)SQL delete duplicate DB-entries WHERE ...

Question

Hi i got a DB like this

DB-name: plzahl
Sample Data:

code   |   plz
------------------
1          8000
1          8000
2          8000
3          8000
2          8001
3          8001
...

What i want is delete all duplicates to leave the one in the table with the highest "code" column value.

i have not yet an unique identifier at the database. would it be better to add one and than use mysql_fetch_array() and build a "deleting array" OR would you use foreach to fetch any "plz", search if count >0 and if, fetch them, sort them and delete the count-1 ones?

so far:

<?php
mysql_connect("localhost", "mysql_user", "mysql_password") or
    die("no connection: " . mysql_error());
mysql_select_db("mydb");

$result = mysql_query("SELECT code, plz FROM plzahl");

while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
    //... here goes the magic
    // make query to search for count $row['plz'] > 0 ?
    ....
}

mysql_free_result($result);
?>

Thanks for any code hint!!

Answer 1

Do it the mysql way:

mysql_query('CREATE TABLE plz_new LIKE plz');
mysql_query('INSERT INTO plz (SELECT MAX(code) AS code, plz FROM plzahl GROUP BY plz)');
mysql_query('DROP TABLE plz');
mysql_query('RENAME TABLE plz_new plz');