Delete duplicate enteries

[Rayn]

How would I go about deleting duplicate enteries in mysql?

For example lets say some of the enteries I have look like this

id | title

12, Hello
13, HI
14, Hello
15, Bye
16, Bye

then I would run the query I need help with and it would look like

id | title

12, Hello
13, HI
16, Bye

?

Thanks for your time
- Rayn

[Pig]

$result = mysql_query("SELECT DISTINCT title FROM table");
while( $row = mysql_fetch_array($result) ) $array[] = $row[0];
foreach($array AS $value) mysql_query("INSERT INTO table (title) values ('$value')");

[LordShryku]

Don't forget to delete the old entries....

[Pig]

Good catch

$result = mysql_query("SELECT DISTINCT title FROM table"); 
while( $row = mysql_fetch_array($result) ) $array[] = $row[0]; 
mysql_query("DELETE FROM table");
foreach($array AS $value) mysql_query("INSERT INTO table (title) values ('$value')");

[drawmack]

me thinks this would be a bit more efficient:

<?php
$sql = "SELECT MIN(ID) FROM table GROUP BY title";
$result = mysql_query($sql);
$num_rows = mysql_num_rows($result);
$unique_ids = array();
for($i=0;$i<$num_rows;$i++) {
    $unique_ids[] = mysql_result($result,$i,0);
}
mysql_free_result($result);
$id_list = implode(',',$unique_ids);
$sql = "DELETE FROM table WHERE ID not in($id_list)";
mysql_query($sql);
?>