0

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'

Blockquote

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

Blockquote

krokodilko
  • 35,300
  • 7
  • 55
  • 79
Chuck
  • 19
  • 6

1 Answers1

0

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;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Gordon, MariaDB/MySQL works sometimes without GROUP BY as well right? I saw this [post](https://stackoverflow.com/questions/1225144/why-does-mysql-allow-group-by-queries-without-aggregate-functions) and [this](https://stackoverflow.com/questions/28497082/mysql-aggregate-functions-without-group-by-clause) – rinz1er Jun 27 '20 at 16:22
  • @ShriramRamesh . . . In this case, it shouldn't work -- it is invalid SQL. I mean, MariaDB might allow the SQL and do something indeterminate, but it is best to keep only full group by mode on and to never rely on such behavior. – Gordon Linoff Jun 27 '20 at 17:29
  • Fantastic...Thanks so much. For my info: what is diff from aggragated and unaggragated ? – Chuck Jun 27 '20 at 18:53