MYSQL Remove Duplicate

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
J0kerz
Forum Commoner
Posts: 37
Joined: Fri May 29, 2009 2:51 pm

MYSQL Remove Duplicate

Post by J0kerz »

Hey there,

I have a function to remove the duplicate 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:

Image

Code: Select all

	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:

Code: Select all

mysql_query("CREATE TABLE new_table as SELECT * FROM test GROUP BY url ORDER BY pr DESC")
Thanks!
User avatar
Sofw_Arch_Dev
Forum Commoner
Posts: 60
Joined: Tue Mar 16, 2010 4:06 pm
Location: San Francisco, California, US

Re: MYSQL Remove Duplicate

Post by Sofw_Arch_Dev »

Would the MAX() mysql aggregate function be handy? Such as:

Code: Select all

CREATE TABLE `foo` AS SELECT t.url, MAX(t.pr) FROM test t GROUP BY t.url;
Post Reply