[SOLVED] Finding duplicates from 2 columns

[JonnoTheDev]

I need a quick solution to extract duplicates into a temporary table for review. The duplicates are to be found where the longitude and latitude values are identical (not just one column). The placenames that differ but have the same long & lat will need their geo data checked so I do not want to delete just yet. Structure:

 

cities

======

cityId

regionId

title

longitude

latitude

 

i.e.

1, 1, citya, 10.1, 45.2

2, 4, cityb, 10.1, 45.2

[kickstart]

Hi

 

Quick attempt. Something like this should do it:-

 

SELECT *
FROM cities a
INNER JOIN (SELECT longitude, latitude, COUNT(title) AS citycount
FROM cities
GROUP BY longitude, latitude) b
ON a.longitude = b.longitude
AND a.latitude = b.latitude
WHERE citycount > 1

 

All the best

 

Keith

[JonnoTheDev]

Thanks, I can work with that result set