Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

In MySQL, I have a table named "city" with "city_name" and "latitude" and "longitude" in it. It appears that I have a few duplicates. What query can I run (in phpmyadmin) that will search through the "city" table for entries containing the same latitude and longitude (with different "city_name"'s)?

share|improve this question

1 Answer 1

Run the query in mysql directly. First access mysql from command line:

sudo mysql -p

Then select the database you want to use

show databases;
use databaseiwant;

Now run a query on the table you need to find duplicates in

show tables;
SELECT fieldtocheckduplicates, COUNT(fieldtocheckduplicates) AS NumOccurrences FROM tabletocheck GROUP BY fieldtocheckduplicates HAVING ( COUNT(fieldtocheckduplicates) > 1 );
share|improve this answer
    
As long as you're not expecting more than a few dozen duplicates, this should work fine. You could write a similar query to delete the duplicates as well. –  Calvin Froedge Jan 30 '11 at 19:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.