Get the MAX and MIN values ​​along with their row id?

Question

I have a table similar to the following

  ID |  temp_hi |  temp_lo
 ------------------------
   0 |  72 |  38
 ------------------------
   1 |  56 |  33
 ------------------------
   2 |  67 |  28

Is there a way in one SQL statement to capture MAX (temp_hi) and its ID and get MIN (temp_lo) and its ID? Therefore, in this case it will be:

(temp_hi id) 0, (temp_hi) 72, (temp_lo id) 2, (temp_low) 28 

Answer 1

You can use a subquery:

 SELECT * FROM data WHERE temp_hi = (SELECT MAX(temp_hi) FROM data) OR temp_lo = (SELECT MIN(temp_lo) FROM data); 

Answer 2

There may be more than the ID with the maximum and minimum temperature, so I just choose one:

 SELECT (SELECT ID FROM temp ORDER BY temp_hi DESC LIMIT 1) AS max_temp_id, MAX(temp_hi) AS max_temp, (SELECT ID FROM temp ORDER BY temp_lo LIMIT 1) AS min_temp_id, MIN(temp_lo) AS min_temp FROM temp 

Check the data to try:

 CREATE TABLE IF NOT EXISTS `temp` ( `ID` int(11) NOT NULL, `temp_hi` int(11) NOT NULL, `temp_lo` int(11) NOT NULL, PRIMARY KEY (`ID`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; INSERT INTO `temp` (`ID`, `temp_hi`, `temp_lo`) VALUES (0, 72, 38), (1, 56, 33), (2, 67, 28); 

Result: