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")
I am looking for a fast way to achieve this because my table have over 200k entries.
have you tried a where clause? As for order, in a table that is meaningless. Data can be placed anywhere, even when you cluster, due to out of order inserts or updates having moved a row to another page. The only time you have a garanteed order is in your select statement with an order by clause. If you are stating that you only want rows in your table that have a higher PR value, then I suggest you look at using a WHERE clause and a subselect that gets the MAX value of the PR column for each URL.