The following SQL query allows me to delete duplicate entries and to re-order with a new ID the rows in my database.
However when deleting duplicate rows I need to KEEP ONLY THE LAST ONE (and delete all older duplicates).
Name, date and time are same among duplicate rows. The only criteria to define the newest row might be its ID or the simple fact that is stored in the database as last row. How can I modify the code to fix it?
$creatmp = mysql_query("CREATE TABLE tmp AS SELECT * FROM mytable WHERE 1 GROUP BY name, date, hour, city, province");
$cancmytable = mysql_query("DROP TABLE mytable");
$datmpamytable = mysql_query("ALTER TABLE tmp RENAME TO mytable");
$creareorderid = mysql_query("CREATE TABLE reorderid LIKE mytable");
$popolareorderid = mysql_query("INSERT INTO reorderid SELECT * FROM mytable ORDER BY date,hour");
$emptyfieldid = mysql_query("ALTER TABLE `reorderid` DROP `id`");
$recountone = mysql_query("ALTER TABLE `reorderid` AUTO_INCREMENT = 1");
$recounttwo = mysql_query("ALTER TABLE `reorderid` ADD `id` int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;");
$emptymytable = mysql_query("DROP TABLE mytable");
$renamereorderid = mysql_query("ALTER TABLE reorderid RENAME TO mytable");