CODEWITHSUNDEEP
sql-server
Francesco Mantovani
Francesco Mantovani

Reputation: 12287

SQL Server: how to select double cities from database

I have an amazing problem on a 3 million rows database.

The database stores all cities in the world but there are doubles:

enter image description here

I would like to SELECT all rows which has the same Latitude and Longitude. How to select the whole row, not just Latitude and Longitude?

I would like to list the results like this:

enter image description here

What I don't want to list the results like this:

SELECT *,ROW_NUMBER() 
OVER (PARTITION BY latitude, Longitude 
ORDER BY latitude, Longitude) AS RN
FROM World

enter image description here

Upvotes: 0

Views: 180

Answers (4)

iltelko
iltelko

Reputation: 57

Group by longitude and latitude and city, use having > 1, and use the result as subquery to filter correct rows from the table.

Select * from world 
 where city IN 
      (Select City From World Group by longitude, latitude, city                        
      having count(* ) > 1 )

Upvotes: 0

ViKiNG
ViKiNG

Reputation: 1334

Try this

;WITH CTE AS(
SELECT Latitude,Longitude,COUNT(Latitude) As NumOfRec
FROM World
GROUP BY Latitude,Longitude
)

SELECT w.* FROM WORLD w 
JOIN CTE c ON w.Latitude=c.Latitude AND w.Longitude=c.Longitude
WHERE c.NumOfRec>1;

Upvotes: 1

Nayas Subramanian
Nayas Subramanian

Reputation: 2379

Hi this will help you,

 select * from World where exists( 
        select * from
            (
            select WorldLatCount = Count(*) 
            from World as WorldLat where World.Longitutde = WorldLat.Longitutde and WorldLat.Latitude = World.Latitude
        ) 
     as WorldCounts where WorldCounts.WorldLatCount> 1

   )

you will get result like this

Country Longitutde  Latitude
ad  42.30   45.50
ad  42.30   45.50
ef  42.78   45.59
ef  42.78   45.59

Upvotes: 0

coolswastik
coolswastik

Reputation: 519

select country, city, accentcity, region, population, region, population, latitude, longitude
from (
      select country
            ,city
            ,accentcity
            ,region
            ,population
            ,latitude
            ,longitude
            ,count(*) over(partition by latitude, longitude) as dup
      from world
     ) as Temp
where dup > 1

Upvotes: 3

Related Questions