Remove Duplicate Records Except One Record

Question

I have a table that has multiple duplicate records as the following:

ID      Title
-----------------
1       Article A
2       Article A
3       Article B
4       Article C
5       Article A

In the above case, i need all duplicate titles and leave one only.

Article B and Article C are fine. I need to remove Articles A, except one.

Sample output:

ID      Title
-----------------
1       Article A
3       Article B
4       Article C

Note: i'm not concerned about which ID to keep or remove. All i want is one record to be pertained.

Assuming i have huge bulk of records that has duplicate titles

Any suggestion?

Answer 1

DELETE 
   t1 
FROM 
   tTable t1, tTable t2 
WHERE 
   t1.fieldName = t2.fieldName AND t1.id > t2.id

ELSE

create another table as below

CREATE TABLE myTable_new (ID INT PRIMARY KEY, Title varchar(20))

and add values as

INSERT INTO myTable_new  (ID, Title) SELECT ID, DISTINCT Title FROM old_table

considering old_table is the earlier table...

Answer 2

This will always leave the row with the lowest ID -

DELETE t2
FROM `table` t1
INNER JOIN `table` t2
    ON t1.Title = t2.Title
    AND t1.ID < t2.ID