CODEWITHSUNDEEP
mysqlsqlduplicates
Angelo Rodriguez
Angelo Rodriguez

Reputation: 127

SQL - Finding Duplicates of One Column But Different in Another Column

How do I find rows that have duplicate values in one column but different values in another column. I have a database of cities with latitude and longitude. I would like to find all rows that have a duplicate in the city name column but have different latitude and longitude. For example, paris,france and paris,texas have the same city name "paris" but different latitude and longitude, so they would both be included in the query results. But it should not return rows that have duplicate city names and duplicate latitude/longitude values.

Upvotes: 2

Views: 5360

Answers (2)

Dhamodharan Subramanian
Dhamodharan Subramanian

Reputation: 211

select city,latitude,longitude,count(*) cnt
from table
group by city,latitude,longitude
having count(*)>1

the cities which come in above resultset are duplicates.

Upvotes: -1

Gordon Linoff
Gordon Linoff

Reputation: 1271023

If you just want the city names:

select city
from table t
group by city
having min(latitude) <> max(latitude) or
       min(longitude) <> max(longitude);

If you want the details, then you can join this back to the main table or use group_concat() to assemble a list of countries (for instance).

Upvotes: 5

Related Questions