Selecting mutiple rows with same values

[grandadevans]

Hello everybody yet again,
I have a table which logs
A) The users IP address
B) The company ID of the company clicked

The table looks like this
[code]
+-----------+--------------+------+-----+-------------------+----------------+
| Field     | Type         | Null | Key | Default           | Extra          |
+-----------+--------------+------+-----+-------------------+----------------+
| clickID   | int(11)      | NO   | PRI | NULL              | auto_increment |
| date      | timestamp    | YES  | MUL | CURRENT_TIMESTAMP |                |
| companyID | int(11)      | YES  | MUL | NULL              |                |
| URL       | varchar(255) | YES  |     | NULL              |                |
| referer   | varchar(50)  | YES  | MUL | NULL              |                |
| IPAddress | varchar(100) | YES  |     | NULL              |                |
| agent     | varchar(255) | NO   | MUL |                   |                |
+-----------+--------------+------+-----+-------------------+----------------+[/code]
How do I select instances of the same IP address with the same compay ID
for example, what I want to do is
DELETE rows where the same IP address and companyID combination appear more than 4 times.

Can anybody help me out?
Thanks
John

[nickk]

Hey I'm in a hurry so I have to be quick. Basically, to delete rows where the same IP address and companyID combination appear more than 4 times, you could select the first row, then loop through all the other rows, comparing the first row to the ones that are looping. Using a counter, you could count the number of times the first row is equal to the others (in terms of IP address and companyID), if the counter is 4=< you delete it. Then you move onto the second row and do the same thing. This is a very rough idea and you could make it much more effecient, maybe there is also a simple mysql statement that could be of more use (I'm not that great at mysql to know it). Hope I helped.

[fenway]

Once you confirm that the following query find all these rows:

[code]SELECT companyID, IPAddress, COUNT(*) AS cnt FROM yourTable GROUP BY companyID, IPAddress HAVING cnt >= 4[/code]

You can easily switch this to a DELETE statement.

BTW, you should be storing your IPs as UNSIGNED INTs and using INET_ATOI().

[grandadevans]

Cheers mate,
I've also implemented the suggestion about storing IP addresses.
John