CODEWITHSUNDEEP
mysql
dstach
dstach

Reputation: 119

Get MAX and MIN values along with their row id?

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 a single SQL statement to grab the MAX(temp_hi) and it's ID and get the MIN(temp_lo) and it's ID? So in this case it would be:

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

Upvotes: 4

Views: 10552

Answers (2)

stivlo
stivlo

Reputation: 85546

There could be more than an ID with max and min temperature, so I just pick 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

Test data to try it:

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:

query results

Upvotes: 3

Raymond Hettinger
Raymond Hettinger

Reputation: 226754

You could use a subquery:

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

Upvotes: 5

Related Questions