Best way to find and delete dups from a mysql table

Hello,

I have a MySQL table containing thousands of records. Many different records have duplicate entries for some column (say it's the article-title column of type varchar). What's the best way to find and delete all records that have the same entry for the article-title column, but of course keeping one record with that article-title-column entry.

I do NOT want to search for a specific article-title-column entry and then delete all records duplicate for that entry but rather, as I stated above, I want to find and then delete all records with duplicate article-title-column entries.

What's the fastest way to do this using PHP functions.

 

One way is to give a distinct query to get unique records on title.

You can also use group by command ( apply it to title ) to see how many duplicate records exists under each title.

Another way is create a new table using the distinct query so you will have a new table with all unique records taken from your old table. The same table structure is maintained in new table.

 

I do not know what you mean by fastest way, but here is my solution to this.

I have table

CREATE TABLE foo (
fooId INT AUTO _INCREMENT NOT NULL,
x INT,
PRIMARY KEY(fooId)
);
INSERT INTO foo(x) VALUES(1);
INSERT INTO foo(x) VALUES(1);
INSERT INTO foo(x) VALUES(2);
INSERT INTO foo(x) VALUES(2);
INSERT INTO foo(x) VALUES(2);

Code (markup):

To remove entry with duplicate x i use such script

$r = mysql_query("SELECT x, count( * ) FROM foo GROUP BY x");
while ($row = mysql_fetch_array($r, MYSQL_NUM)) 
{
    if($row[1]>1)
    {
	  $x = $row[0];
	  $y = $row[1]-1;
	  mysql_query("DELETE FROM foo WHERE x=$x LIMIT $y");
    }
}

PHP:

 

|||***SOLVED! THANKS!***|||​

I AM BORED!!!​