问题描述:

I have database with outside temperature. Rows - id, datetime and temperature, there is thousand records. How to find highest temperature from last n rows?

I´d like to find highest value from last 50 records in table, by this command:

SELECT DATETIME, temperature from table ORDER BY DATETIME DESC LIMIT 50;

this command not work.

网友答案:
SELECT max(temperature) 
from your_table 
ORDER BY DATETIME DESC 
LIMIT 50;
网友答案:
SELECT max(temperature) from table ORDER BY DATETIME DESC LIMIT 50;

or if you want date wise temperature

SELECT Date(DATETIME), max(temperature) from table ORDER BY DATETIME DESC LIMIT 50 group by Date(DATETIME);
网友答案:

Try this

SELECT max(temperature) 
from table
ORDER BY DATETIME DESC 
LIMIT 50 1;
网友答案:

i try our advice, but when i in php write:

echo $row['DATETIME']. $row['temperature']. "<br />" ;

on the page is only error: Notice: Trying to get property of non-object

网友答案:
SELECT
    MAX(temperature)
FROM (
    SELECT
        temperature
    FROM
        table
    ORDER BY
        id DESC
    LIMIT 50
) t

Sorting by id would be quicker than by DATETIME in case bigger DATETIME always have bigger id

相关阅读:
Top