On 20 Feb 13:55, Brett Parker wrote:
test=# select * from temps where id in ((select id from temps where extract(year from date) = 2012 order by out_temp asc,date asc limit 1) union (select id from temps where extract(year from date) = 2012 order by out_temp desc,date asc limit 1)); id | date | time | in_temp | out_temp ----+------------+----------+---------+---------- 86 | 2012-05-30 | 07:01:00 | -3 | -30 94 | 2012-07-02 | 12:02:00 | -23 | 29
Or better:
test=# select min_out_temp_date,min_out_temp,max_out_temp_date,max_out_temp from ((select 1 as id,date + time as min_out_temp_date,out_temp as min_out_temp from temps where extract(year from date) = 2012 order by out_temp asc,date asc limit 1) as min left join (select 1 as id,date + time as max_out_temp_date,out_temp as max_out_temp from temps where extract(year from date) = 2012 order by out_temp desc,date asc limit 1) as max on min.id=max.id); min_out_temp_date | min_out_temp | max_out_temp_date | max_out_temp ---------------------+--------------+---------------------+-------------- 2012-05-30 07:01:00 | -30 | 2012-07-02 12:02:00 | 29 (1 row)
Which gives you what you want in one result set... It gives the first date for the min and max temps, and joins the date with the time.