sqlmariadb

incorrect results from MAX and ORDER BY

I'm trying to get the maximum temperature in a 200 record count along with the temperature. I'm successful in getting the max(temperature) but the date is incorrect. There must be some and-ing in there where temperature = temperature and DateAndTime = DateAndTime.

SELECT DateAndTime, max(Temperature) as MaxTemp, Humidity, BarrPress 
FROM (
      select DateAndTime, Temperature, Humidity,BarrPress 
      from `temp-at-interrupt` 
      order by DateAndTime DESC LIMIT 200
) as T
Solution

  • Your query should fail, because you have mixed aggregated columns and unaggregated columns -- and there is no group by.

    Just use order by and limit:

    select t.*
from (select t.*
      from `temp-at-interrupt` t
      order by dateandtime desc
      limit 200
     ) t
order by temperature desc
limit 1;