select max(number) and where timestamp>yesterday mixes up the results/rows

Question

I have a simple table:

id	temperature	timestamp
17	23	1630770051
18	24	1630772051
4799	35	1632140689

I want max(temperature) from today or yesterday

Example 1)

SELECT max(temperature), DAY(FROM_UNIXTIME(timestamp))
FROM `zimmer_raumdaten`
group by DAY(FROM_UNIXTIME(timestamp));

max(temperatur) DAY(FROM_UNIXTIME(timestamp))
26.70 4
26.60 5
23.90 6
44.10 7
28.00 8
35.30 9
37.80 10
31.60 11
36.70 12
36.60 13
38.30 14
26.90 15
27.10 16
46.00 17
47.90 18
23.00 19
25.00 20

Result looks good.

But when I`m trying to fetch only one result for example for today - the result mixes up the rows.

SELECT max(temperature), DAY(FROM_UNIXTIME(timestamp)), id
FROM `zimmer_raumdaten`
where DAY(FROM_UNIXTIME(timestamp)) = DAY(NOW());

max(temperatur)	DAY(FROM_UNIXTIME(timestamp))	id
25.00	20	4977

The id is not correct - the row 4977 has temperature 19.00.

I appreciate every kind of ideas/thoughts on the SQL Statement.

cheers

Your second query is malformed and can only run in MySQL prior to version 5.7.5: the problem is that `MAX()` cannot be used in the absence of a `GROUP BY` when there are other non-aggregated columns. — The Impaler, Sep 20 '21 at 13:48
Can you reproduce the case in https://www.db-fiddle.com/ ? It may be an issue with the version of MySQL you are using. I tried in MySQL 8 and it works as expected. See example at https://www.db-fiddle.com/f/8gjAozA9ga1HmBFV4zVjss/0 — The Impaler, Sep 20 '21 at 13:48

score 0 · Answer 1 · answered Sep 20 '21 at 13:59

You are misusing MySQL's notorious nonstandard extension to GROUP BY.

Under that extension's rule, your second query is equivalent to using ANY_VALUE() on your id column. That means MySQL is free to choose any row to fulfill that value.

SELECT max(temperature), DAY(FROM_UNIXTIME(timestamp)), 
       ANY_VALUE(id)     /* <-   bad. bad. bad. */
FROM `zimmer_raumdaten`
where DAY(FROM_UNIXTIME(timestamp)) = DAY(NOW());

If you want to find the id of the row containing the highest temperature for the day in question, you need a subquery.

               SELECT MAX(temperature) temperature,
                      DAY(FROM_UNIXTIME(timestamp))) day
                 FROM zimmer_raumdaten
                GROUP BY DAY(FROM_UNIXTIME(timestamp)))

Then you incorporate that query into a main query to recover the detail row from your table matching that temperature.

SELECT a.temperature, 
       b.day,
       a.id
  FROM zimmer_raumdaten a
  JOIN (
               SELECT MAX(temperature) temperature,
                      DAY(FROM_UNIXTIME(timestamp))) day
                 FROM zimmer_raumdaten
                GROUP BY DAY(FROM_UNIXTIME(timestamp)))
       ) b  ON a.temperature = b.temperature
           AND DAY(FROM_UNIXTIME(a.timestamp)) = b.day
 WHERE b.day = DAY(NOW())

select max(number) and where timestamp>yesterday mixes up the results/rows

1 Answers1