Re: MySQL Query Puzzle

Just to add more perspective : You will have a table with DISTINCT values. Drop the initial table (better take a back-up); copy from the temporary table which will have only DISTINCT values. Regards, Shreyas On Mon, Jul 19, 2010 at 7:58 PM, Shreyas Agasthya <shreyasbr@gmail.com>wrote: > How about this : > > CREATE TEMPORARY TABLE bad_temp1 (id INT,name VARCHAR(20)); > INSERT INTO bad_temp1 (id,name) SELECT DISTINCT id,name FROM SAMPLE; > > Regards, > Shreyas > > On Mon, Jul 19, 2010 at 7:31 PM, 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.. >> > >> > Regards >> > Peter >> > >> >> If your table had a db generated sequential unique identifier (an >> identity / autoinc), then something along these lines may be what you >> are looking for ... >> >> -- Delete everything except the UniqueIDs we want to keep. >> DELETE FROM >> Table >> WHERE >> UniqueID NOT IN >> ( >> -- Just get the UniqueIDs we want to keep. >> SELECT >> UniqueID >> FROM >> ( >> -- Get the earlist UniqueID for each Col1, Col2, >> pairing. >> SELECT >> Col1, >> Col2, >> MIN(UniqueID) AS UniqueID >> FROM >> Table >> GROUP BY >> Col1, >> Col2 >> ) >> ) >> >> UNTESTED!!!! >> >> -- >> PHP General Mailing List (http://www.php.net/) >> To unsubscribe, visit: http://www.php.net/unsub.php >> >> > > > -- > Regards, > Shreyas Agasthya > -- Regards, Shreyas Agasthya