title: SQL Server: SELECT row once only where two values are the same
body: <p>I'm working on an <strong>OpenSource</strong> and <strong>free database</strong> provided by <a href="http://lite.ip2location.com/database/ip-country-region-city-latitude-longitude-zipcode-timezone" rel="nofollow noreferrer">IP2Location</a> that lists tons of IP addresses from internet. </p>
  
  <p>You can download it from <a href="http://lite.ip2location.com/database/ip-country-region-city-latitude-longitude-zipcode-timezone" rel="nofollow noreferrer">here</a>.</p>
  
  <p>My main interest aren't IP addresses: I want to have 1 geographical coordinate for each city per country. </p>
  
  <p>The query I have now returns me doubles: </p>
  
  <pre><code>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
  </code></pre>
  
  <p>Which is very annoying:</p>
  
  <p><a href="https://i.stack.imgur.com/LxTSk.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/LxTSk.png" alt="enter image description here"></a></p>
  
  <p>One row with one <strong>latitude</strong> and <strong>longitude</strong> per city will be fine: I just want to put a pin on a map</p>