SQL Server: SELECT row once only where two values are the same

Question

I'm working on an OpenSource and free database provided by IP2Location that lists tons of IP addresses from internet.

You can download it from here.

My main interest aren't IP addresses: I want to have 1 geographical coordinate for each city per country.

The query I have now returns me doubles:

WITH cte AS
(   
    -- Let's pass rows that have the same latitude and longitude
    SELECT *, ROW_NUMBER() OVER (PARTITION BY latitude, longitude 
                            ORDER BY latitude, longitude) AS rn
    FROM ip2location_db11
    -- Avoid rows without city name
    where city_name != '-'          
)

SELECT
-- These are the only columns I'm interested in from the whole ip2location_db11 database
ROW_NUMBER() OVER (ORDER BY country_code desc,city_name desc) as countdown_order,
latitude,longitude,city_name,country_code
FROM cte
-- Let's take the first row where latitude and longitude are the same
WHERE rn = 1
-- I want to order results by city name
order by countdown_order desc

Which is very annoying:

One row with one latitude and longitude per city will be fine: I just want to put a pin on a map

Just use row_number() over(partition by city_name order by countdown_order) Then you will only get 1 row per city - do it in a outer select of the current query — SqlKindaGuy, Jul 27 '18 at 11:55

score 1 · Accepted Answer · answered Jul 27 '18 at 11:59

Edit If you can have the same city in different country_code, you just write that into the partition by as well. Otherwhise it should be something like this:

WITH cte AS
(   
    -- Let's pass rows that have the same latitude and longitude
    SELECT *, ROW_NUMBER() OVER (PARTITION BY latitude, longitude 
                            ORDER BY latitude, longitude) AS rn
    FROM ip2location_db11
    -- Avoid rows without city name
    where city_name != '-'          
)
,columnsneeded as (

SELECT
-- These are the only columns I'm interested in from the whole ip2location_db11 database
ROW_NUMBER() OVER (ORDER BY country_code desc,city_name desc) as countdown_order,
latitude,longitude,city_name,country_code
FROM cte
-- Let's take the first row where latitude and longitude are the same
WHERE rn = 1
)

Select countdown_order,latitude,longtitude,city_name,country_code 
from(
Select *,ROW_NUMBER() over(partition by city_name order by countdown_order) as rn1 from columnsneeded
)x where rn1 = 1
-- I want to order results by city name
order by countdown_order desc

I particularly liked your query because it makes the job done exactly as I wanted. And it just takes 9 seconds to execute. Thank you — Francesco Mantovani, Jul 27 '18 at 20:20

score 1 · Answer 2 · answered Jul 27 '18 at 12:20

1

I do not know, whether your first column (countdown_order) has any significance, or just a unique identifier for the row...

Anyway, if you just want to have a city/country with a pair of co-ordinates, you probably should use GROUP BY with the AVG() aggregate function to average the co-ordinates of the given city...

SELECT AVG(latitude) AS latitude, AVG(longitude) AS longitude, city_name, country_code
FROM ip2location_db11
GROUP BY country_code, city_name
ORDER BY country_code, city_name

answered Jul 27 '18 at 12:20

Usagi Miyamoto

6,196
1
19
33

Even if your query isn't exactly what I wanted I really appreciated the `AVG()`. I think it is a genius idea even if the query execution is slow (> 40 sec). Thank you, you teach me something today – Francesco Mantovani Jul 27 '18 at 20:23

score 0 · Answer 3 · answered Jul 27 '18 at 12:00

All you need to do is use ROW_NUMBER partitioned by city (and ordered by anything :)) and take only those equal to one in outer query (which you have to wrap around yours). Try this :

SELECT countdown_order,latitude,longitude,city_name,country_code FROM (
    SELECT
    -- These are the only columns I'm interested in from the whole ip2location_db11 database
    ROW_NUMBER() OVER (ORDER BY country_code desc,city_name desc) as countdown_order,
    ROW_NUMBER() OVER (PARTITION BY city_name ORDER BY latitude) rnTofilter,
    latitude,longitude,city_name,country_code
    FROM cte
    -- Let's take the first row where latitude and longitude are the same
    WHERE rn = 1
    -- I want to order results by city name
) a WHERE rnToFilter = 1
order by countdown_order desc

Hi @Michal, the query returns me `Msg 208, Level 16, State 1, Line 1 Invalid object name 'cte'.`. If I then change `FROM cte` to `FROM ip2location_db11` it returns me `Msg 207, Level 16, State 1, Line 9 Invalid column name 'rn'.` And from there I don't know what to change. But I see what you want to do. Thank you for your help — Francesco Mantovani, Jul 27 '18 at 20:26

SQL Server: SELECT row once only where two values are the same

3 Answers3