John Smith John Smith - 4 months ago 10
SQL Question

MySQL, remove duplicate lines (must be unique by 2 columns)

I have this:

1899, 184, 531 *
1900, 184, 531 *
1901, 113, 531
1902, 184, 436


I want to get

1899, 184, 531
1901, 113, 531
1902, 184, 436


without temp tables, etc. How to?

Answer

If you want to do a SELECT where you filter out the duplicates, you can use this:

SELECT DISTINCT Col1, Col2 FROM table_name ORDER BY Id;

SQL fiddle for SELECT

Or if you want a DELETE query, you can do this:

DELETE t1 FROM table_name t1, table_name t2
WHERE t1.Col1 < t2.Col1
AND t1.Col2 = t2.Col2 
AND t1.Col3 = t2.Col3;

SQL fiddle for DELETE