MySQL Forums
Forum List  »  Newbie

Remove duplicate rows
Posted by: John Smith
Date: June 11, 2010 09:38AM

Hey there,

I have a code to remove the duplicates in one of my table but I cant seem to be able to ORDER the way mysql select the values.

Here is my initial table: http://img196.imageshack.us/img196/2421/tablesw.jpg

=========================================================================
mysql_query("CREATE TABLE new_table as SELECT * FROM test GROUP BY url")
mysql_query("DROP TABLE test")
mysql_query("RENAME TABLE new_table TO test")
=========================================================================

I want to remove the duplicated URL and make sure that it keeps only the value with the higher PR. I tried using the ORDER BY attribute but it doesnt seem to work. The first 3 values with a PR of 0 are always selected and placed in the new table...

The good result should be that the 3 values (a,b,c) with a PR of 6 are found in the final table.

I tried this and it doesnt work:
=========================================================================
mysql_query("CREATE TABLE new_table as SELECT * FROM test GROUP BY url ORDER BY pr DESC")
=========================================================================

Thanks!

Options: ReplyQuote


Subject
Written By
Posted
Remove duplicate rows
June 11, 2010 09:38AM
June 11, 2010 09:44AM
June 11, 2010 11:26AM
June 11, 2010 11:38AM
June 11, 2010 02:21PM
June 11, 2010 03:41PM
June 13, 2010 01:27PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.