Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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


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.