Remove duplicated row

[Dieuz]

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:

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.

Thanks!

 

[towerbase]

I think you need to use a Correlated Subquery. Try

SELECT DISTINCT
  url
, pr
FROM
  test as t
WHERE
  pr = (
    SELECT
      MAX(pr)
    FROM
      test
    WHERE
      url=t.url
  )

I didn't really understand Correlated Subqueries until I read Rudy Limeback's excellent book 'Simply SQL'.

Andrew
Hampshire, UK