Skip to content

Instantly share code, notes, and snippets.

@madelfio
Last active December 19, 2015 07:59
Show Gist options
  • Save madelfio/5922209 to your computer and use it in GitHub Desktop.
Save madelfio/5922209 to your computer and use it in GitHub Desktop.
most common city names in US
     name     | cnt | minpop | maxpop 
--------------+-----+--------+--------
 Franklin     |  32 |     92 |  62487
 Fairview     |  30 |     60 |  13835
 Clinton      |  28 |     41 |  99753
 Salem        |  27 |     91 | 154637
 Madison      |  25 |    204 | 233209
 Greenville   |  24 |     75 |  84554
 Georgetown   |  24 |    124 |  47400
 Marion       |  24 |    133 |  36837
 Springfield  |  24 |    158 | 159498
 Arlington    |  23 |     25 | 365438
 Riverside    |  23 |     35 | 303871
 Chester      |  21 |    117 |  33972
 Manchester   |  21 |     57 | 109565
 Mount Vernon |  21 |    145 |  67292
 Fairfield    |  21 |    113 | 105321
 Burlington   |  21 |    152 |  49963
 Milton       |  21 |     58 |  32661
 Milford      |  21 |     26 |  52759
 Jackson      |  20 |    223 | 173514
 Clayton      |  20 |      7 |  16116
 Centerville  |  20 |     27 |  23999
 Newport      |  20 |     97 |  24672
 Ashland      |  20 |     66 |  21925
 Washington   |  20 |    135 |  18761
 Oxford       |  20 |     48 |  21371
 Kingston     |  20 |     32 |  23893
 Troy         |  19 |     93 |  80980
 Farmington   |  19 |    110 |  45877
 Hudson       |  19 |    129 |  22262
 Dayton       |  19 |     52 | 141527
 Hillsboro    |  19 |     80 |  91611
 Auburn       |  19 |     48 |  70180
 Oakland      |  19 |    225 | 390724
 Jefferson    |  19 |     12 |  28487
 Danville     |  18 |     34 |  43055
 Dover        |  18 |    103 |  36047
 Monroe       |  18 |    160 |  48815
 Lexington    |  18 |    228 | 225366
 Princeton    |  18 |     62 |  22038
 Waverly      |  18 |     37 |  23925
 Cleveland    |  18 |     83 | 396815
 Liberty      |  18 |     76 |  29149
 Albany       |  18 |     55 |  97856
 Richmond     |  18 |    464 | 204214
 Jamestown    |  17 |     10 |  31146
 Columbia     |  17 |     83 | 129272
 Winchester   |  17 |     61 |  27978
 Weston       |  17 |     55 |  65333
 Monticello   |  17 |     98 |  12759
 Florence     |  17 |     38 |  39319

SQL:

select
  name,
  count(*) cnt, 
  min(population) minpop, 
  max(population) maxpop
from geoname 
where 
  country = 'US' and 
  fclass = 'P' and 
  population > 0 
group by name
order by 2 desc
limit 50;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment