Jump to content

need a query for removing dupes


webosb

Recommended Posts

i cant seem to figure out how i can do this but this is what i need done.

I have 3 fields: id, rating, and ip

 

sample format:

 

id | rating | ip

1 | 10 | 1.2.3.4

1 | 10 | 1.2.3.4

1 | 10 | 1.2.3.4

1 | 10 | 1.2.3.4

1 | 10 | 1.3.2.4

1 | 10 | 1.1.2.2

 

I need a query to remove all duplicate ips for rating 10 for id 1 so the above sample will end up like this:

 

id | rating | ip

1 | 10 | 1.2.3.4

1 | 10 | 1.3.2.4

1 | 10 | 1.1.2.2

 

please note that id ranges from 1-1000 and rating ranges anywhere from 1 to 10 so I need a query to remove dupe ip's from ratings 1-10 for each id

Link to comment
Share on other sites

That is oddly stored data, consider grabing a count of each group that has duplicate records, like this:

 

SELECT count(*) - 1 as delete_number, concat(id, rating, ip) as duplicate_row 
FROM ips
GROUP BY concat(id, rating, ip)
HAVING count(*) > 1;

 

... and then deleting each duplicate ip record separately with a limit

 

mysql_query($query = "DELETE FROM ips WHERE concat(id, rating, ip) = '{$record['duplicate_row']}' LIMIT {$record['delete_number']}") or trigger_error(mysql_error()."<PRE>".$query."</PRE>", E_USER_ERROR);

 

EDIT: probably best to add an appropriate separator for the concat()

Link to comment
Share on other sites

I assume that you have used varchar data type for ip

ALTER IGNORE TABLE ips CHANGE ip ip VARCHAR(10) UNIQUE NOT NULL;

as per my knowledge it works.

 

That's dangerous, you have no idea which records it will keep!

Link to comment
Share on other sites

?

I mean, according the query I have written there It removes all the records except the first occurrence of each unique ip .As each ip has a unique id and rank so it will keep the records of unique (id, rating, ip) combination.

Link to comment
Share on other sites

?

I mean, according the query I have written there It removes all the records except the first occurrence of each unique ip .As each ip has a unique id and rank so it will keep the records of unique (id, rating, ip) combination.

webosb original post does not mention that each ip has a unique id - but does mention that the ids range from 1 - 1000 and rating range from 1 - 10.

Link to comment
Share on other sites

webosb original post does not mention that each ip has a unique id - but does mention that the ids range from 1 - 1000 and rating range from 1 - 10.

OK, I Agree. I assumed that each ip will have unique id and rank as it make sense.

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.