Re: MySQL Query Puzzle

On 19 July 2010 15:01, Richard Quadling <rquadling@gmail.com> wrote: > On 19 July 2010 05:44, Peter <peters@egrabber.com> wrote: >> Hi All, >> >> I have a  table which contain's some duplicate rows. I just want to delete >> the duplicate records alone >> not original records. >> >> Assume my table as look as below >> >> column1 column2 >> 1 >>        a >> 1 >>        a >> 2 >>        b >> 3 >>        c >> 3 >>        c >> >> >> >> i want the above table need  to be as below, After executing the mysql >> query. >> >> column1 >>        column2 >> 1 >>        a >> 2 >>        b >> 3 >>        c >> >> >> >> >> Thanks in advance.. >> Slightly more concise ... -- Delete everything except the UniqueIDs we want to keep. DELETE FROM Table WHERE UniqueID NOT IN ( -- Get the earliest UniqueIDs for each Col1, Col2 pairing. SELECT MIN(UniqueID) FROM Table GROUP BY Col1, Col2 ) http://www.devx.com/tips/Tip/14665 DELETE Table FROM Table T1, Table T2 WHERE T1.Col1 = T2.Col1 AND T1.Col2 = T2.Col2 AND T1.UniqueID > T2.UniqueID http://www.cryer.co.uk/brian/sql/sql_delete_duplicates.htm etc. Many different ways. http://www.orafaq.com/faq/how_does_one_eliminate_duplicates_rows_from_a_table Method 3 should be the fastest.