Request multi column min/max with date in SQL

Question

I have a SQL table which look like :

Date        temp    dewpoint    humidity
10/12/15    5.1     2.4         57
11/12/15    4.1     5.8         69
12/12/15    20.6    3.6         56
13/12/15    13.0    2.5         21
14/12/15    5.6     13.6        15
15/12/15    5.1     2.4         56

I would like to know it it's possible to print this out with only one SQL query. That is for each data column, get the min and max value with the occurrence date (As an example I would like to know that the min 4.1 was on 11/12/15 and the max was 20.6 on 12/12/15.)

Would it be possible to do this with one sql query? Specifically, I want the output format as:

Date        dataName     dataValue
11/12/15    temp-min     4.1
12/12/15    temp-max     20.6
14/12/15    humidity-min 15
11/12/15    humidity-max 69

Answer 1

In MySQL, the easiest way is probably to use the substring_index()/group_concat() trick:

select substring_index(group_concat(date order by temp asc), ',', 1) as minTempDate,
       substring_index(group_concat(date order by temp desc), ',', 1) as maxTempDate,
       substring_index(group_concat(date order by dewpoint asc), ',', 1) as minDPDate,
       substring_index(group_concat(date order by dewpoint desc), ',', 1) as maxDPDate,
       substring_index(group_concat(date order by humidity asc), ',', 1) as minHumidityDate,
       substring_index(group_concat(date order by humidity desc), ',', 1) as maxHumidityDate
from table t;

An alternative is to use union all like this:

(select date, 'temp-min', temp from table t order by temp asc limit 1)
union all
(select date, 'temp-max', temp from table t order by temp desc limit 1)
union all
(select date, 'humidity-min', humidity from table t order by humidity asc limit 1)
union all
(select date, 'humidity-max', humidity from table t order by humidity desc limit 1)