Question 209116 in Mysql Question posted in
The official MySQL documentation can be found here.

MYSQL get max. values from a specific date

I have a table with multi-year data of temperature measurement.

Looking for the highest measured temperatures including the date of recording from the current year only.

Tried following:

SELECT timestamp, temperature AS max_temp
FROM table
WHERE
temperature = (SELECT max(temperature) FROM table
            WHERE timestamp >= '2023-01-01');

But now records from complete table are returned with higest measured temperature in 2023.

Who has the right way to go?

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    Solved:

    SELECT timestamp, temperature AS max_temp FROM table WHERE timestamp >= '2023-01-01' AND temperature = (SELECT max(temperature) FROM table WHERE timestamp >= '2023-01-01');


  2. 0

    You can use Year() function to extract the year portion of the timestamp:
    So, the modified query would be :

    SELECT timestamp, temperature AS max_temp
    FROM table
    WHERE
    temperature = (SELECT max(temperature) FROM table
    WHERE Year(timestamp) = YEAR(CURDATE()));

    Hope it works!

    Login or Signup to reply.
  3. 0

    this may solve your problem:

    SELECT timestamp,temperature 
FROM tableName 
WHERE id IN ( SELECT id 
             FROM tableName 
             WHERE temperature IN (SELECT MAX(temperature) 
                                    FROM tableName 
                                    WHERE LEFT(timestamp,4)='2023')) 
     AND LEFT(timestamp,4)='2023';
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search