Detecting duplicates across fields

Forum Moderators: open

Message Too Old, No Replies

Detecting duplicates across fields

MySQL query to match multiple columns

trillianjedi

6:41 pm on Feb 16, 2008 (gmt 0)

This is a tricky one for someone of my average skill set with MySQL.

I'm detecting multiple signups and at the moment I'm looking at interesting correlations between IP based data and a few other things. I'm after a match where all columns are the same.

For example, let's say I have the following 4 fields in a table:-

ISP name
Lattitude
Longitude
Browser User-Agent

Is it possible with a single query to pull all records from a database where those 4 fields are identical (when I don't know in advance what the values are)?

Thanks!

ZydoSEO

5:13 pm on Feb 17, 2008 (gmt 0)

Sure... There are several ways... Try something like:

SELECT isp_name, longitude, latitude, browser_user_agent, COUNT(*)
FROM mytable
GROUP by isp_name, longitude, latitude, browser_user_agent
HAVING COUNT(*) > 1

trillianjedi

1:35 pm on Feb 19, 2008 (gmt 0)

Brilliant - thanks. Have managed to get that working.