How do I join two tables based on a minimum value in the first table?

I have two tables: one has a list of weather stations near major cities and the distance to the city, and the other has weather averages for each weather station. I want to do a join such as showing weather data from the station closest to San Francisco.

Example table distances:

select * from distances limit 3;

   city   |   station   | distance  
----------+-------------+-----------
 New York | USC00280721 | 62.706849
 New York | USC00280729 | 91.927548
 New York | USC00280734 | 91.865147

Example table weatherdata

select * from weatherdata where id='USC00280734' limit 3;

     id      |    date    | element | data_value | mflag | qflag | sflag | observation_time 
-------------+------------+---------+------------+-------+-------+-------+------------------
 USC00280734 | 2001-01-01 | TMAX    |         -6 |       |       | 0     | 07:00:00
 USC00280734 | 2001-01-01 | TMIN    |        -61 |       | I     | 0     | 07:00:00
 USC00280734 | 2001-01-01 | TOBS    |        -89 |       | I     | 0     | 07:00:00

I’d like to be able to do an SQL select based on the city name.

Answer

For one city, I would suggest:

select wd.*
from (select d.*
      from distances d
      where city = 'San Francisco'
      order by distance
      limit 1
     ) d join
     weatherdata wd
     on wd.id = s.station;

For all or multiple cities, I would just tweak this using distinct on:

select wd.*
from (select distinct on (city) d.*
      from distances d
      order by city, distance
     ) d join
     weatherdata wd
     on wd.id = s.station;

Both of these version can use an index on distances(city, distance).