need a query for removing dupes

[webosb]

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

[bubblegum.anarchy]

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()

[webosb]

I kept getting an error from the mysql_query part...

 

 

can I accomplish this without the use of php?

 

[fenway]

Just echo the $query first.

[Illusion]

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.

[fenway]

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!

[webosb]

ack.. I just ended up exporting a csv file and then i filtered all the columns with excel... thanks for the help everyone. i appreciate it.

[Illusion]

you have no idea which records it will keep!

It will keep the records with first occurence of each unique ip

[bubblegum.anarchy]

webosb wanted a unique (id, rating, ip) combination.

[Illusion]

Obviously it results those records only as long as each ip has unique rank and id.

[bubblegum.anarchy]

Obviously it results those records only as long as each ip has unique rank and id.

?

[Illusion]

?

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.

[bubblegum.anarchy]

?

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.

[Illusion]

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.