MySQL Delete Rows With Duplicate Content

Question

Possible Duplicate:
Remove duplicate rows in MySQL
Delete all Duplicate Rows except for One in MySQL?

This is how my table looks like

1 | 1 | Hello
1 | 2 | Hello
1 | 3 | Hello
1 | 4 | some text
1 | 5 | text
1 | 6 | text

How can I delete all duplicate rows and just leave the last one; for example:

1 | 3 | Hello
1 | 4 | some text
1 | 6 | text

Any help would be greatly appreciated.

Please look to the Related questions bar in the right. – zerkms Jan 01 '13 at 11:39 — zerkms, Jan 01 '13 at 11:39

score 1 · Answer 1 · answered Jan 01 '13 at 11:41

DELETE  a
FROM    tableName a
        LEFT JOIN
        (
            SELECT col3, col1, MAX(col2) maxCol
            FROM tableName
            GROUP BY col3, col1
        ) b ON a.col1 = b.col1 AND
                a.col2 = b.maxCol AND
                a.col3 = b.col3
WHERE   b.col1 IS NULL

SQLFiddle Demo

MySQL Delete Rows With Duplicate Content

1 Answers1