title: Delete duplicates based on string length criteria
body: <h2>Background</h2>
  
  <p>Remove duplicate city names from a temporary table, based on the length of the name.</p>
  
  <h2>Problem</h2>
  
  <p>The following query returns 350,000 rows:</p>
  
  <pre><code>select
    tc.id,
    tc.name_lowercase,
    tc.population,
    tc.latitude_decimal,
    tc.longitude_decimal
  from
    climate.temp_city tc
  inner join (
    select
      tc2.latitude_decimal,
      tc2.longitude_decimal
    from
      climate.temp_city tc2
    group by
      tc2.latitude_decimal,
      tc2.longitude_decimal
    having
      count(*) &gt; 3
  ) s on 
    tc.latitude_decimal = s.latitude_decimal and
    tc.longitude_decimal = s.longitude_decimal
  </code></pre>
  
  <p>Sample data:</p>
  
  <pre><code>940308;"sara"            ;;-53.4333333;-68.1833333
  935665;"estancia la sara";;-53.4333333;-68.1833333
  935697;"estancia sara"   ;;-53.4333333;-68.1833333
  937204;"la sara"         ;;-53.4333333;-68.1833333
  940350;"seccion gap"     ;;-52.1666667;-68.5666667
  941448;"zanja pique"     ;;-52.1666667;-68.5666667
  935941;"gap"             ;;-52.1666667;-68.5666667
  935648;"estancia gap"    ;;-52.1666667;-68.5666667
  939635;"ritchie"         ;;-51.9833333;-70.4
  934948;"d.e. ritchie"    ;;-51.9833333;-70.4
  934992;"diego richtie"   ;;-51.9833333;-70.4
  934993;"diego ritchie"   ;;-51.9833333;-70.4
  934990;"diego e. ritchie";;-51.9833333;-70.4
  </code></pre>
  
  <p>I would like to remove all duplicates, retaining the rows where:</p>
  
  <ul>
  <li>population is not null; and</li>
  <li>the name is the longest of the duplicates (<code>max(tc.name_lowercase)</code>); and</li>
  <li>if neither of these conditions are met, retain <code>max(tc.id)</code>.</li>
  </ul>
  
  <p>From the given set of data, the remaining rows would be:</p>
  
  <pre><code>935665;"estancia la sara";;-53.4333333;-68.1833333
  935648;"estancia gap"    ;;-52.1666667;-68.5666667
  934990;"diego e. ritchie";;-51.9833333;-70.4
  </code></pre>
  
  <h2>Question</h2>
  
  <p>How would you select just the rows with duplicate lat/long values that meet the problem criteria?</p>
  
  <p>Thank you!</p>