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?
If you want to do a SELECT where you filter out the duplicates, you can use this:
SELECT DISTINCT Col2, Col3 FROM table_name ORDER BY Id;
Or if you want a DELETE query, you can do this:
DELETE t1 FROM table_name t1, table_name t2
WHERE t1.Id < t2.Id
AND t1.Col2 = t2.Col2
AND t1.Col3 = t2.Col3;
SELECT MIN(Col1), Col2, Col3
FROM yourTable
GROUP BY Col2, Col3
This assumes that you chose the 1899 record over the 1900 one because the former is the smaller of the two.
If you want to delete the duplicate records, as you have defined them as duplicates, from your table, then you can use the following query:
DELETE t1.*
FROM yourTable t1
LEFT JOIN
(
SELECT MIN(Col1) AS Col1, Col2, Col3
FROM yourTable
GROUP BY Col2, Col3
) t2
ON t1.Col1 = t2.Col1 AND t1.Col2 = t2.Col2 AND t1.Col3 = t2.Col3
WHERE t2.Col1 IS NULL
select min(f1) as f1, f2, f3 from tbl group by f2, f3
For a DELETE:
DELETE
FROM table1 t
WHERE EXISTS (SELECT u.*
FROM table1 u
WHERE u.col2 = t.col2 AND u.col3 = t.col3
AND u.col1 > t.col1)
There of course is the problem of deleting on the same table as the subquery.
For DELETE:
DELETE t1
FROM yourtable t1
JOIN yourtable t2
ON t1.col2 = t2.col2
AND t1.col3 = t2.col3
AND t1.col1 > t2.col1
This can delete duplicate row.
delete from table_name where column1 in(select column1 from (select column1 from table_name group by column1,column2 having count(*)>1) t);