CODEWITHSUNDEEP
sqlpostgresql
Villahousut
Villahousut

Reputation: 135

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.

Upvotes: 0

Views: 214

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

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).

Upvotes: 1

TrevB
TrevB

Reputation: 40

I would create a 3rd join from distance with just the city and min(distance) and join back to the distances table.

select wd.* 
from weatherdata wd
join distances d on d.station = wd.id
join (select City, min(distinace) mindistance
      from distances
      group by city) A on a.city = d.city
                       and a.mindistance = d.distance
where d.city = 'San Francisco'

Upvotes: 1

Rajeev Pande
Rajeev Pande

Reputation: 476

You may try below -

select * from weatherdata wd where id in
(
select station 
from distances d 
where city = 'San Francisco'
and distance in (Select min(distance) from distances where city = 'San Francisco')
);

Upvotes: 1

Related Questions