SQL code error (OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY)

Question

I'm trying to determine an average temperature over one day, for home assistant. I have linked sensors to home assistant, which send data to phpmyadmin. I can't send the sensors separately via home assistant, but they are all listed in the folder status. See below.

sensors in folder states

By sending a sql code via home assistant, I can search values in the database. So now I'm trying to find the average of the last 100 measurements (+/- 1 day). To achieve this, I have written the following sql code.

SELECT AVG(`state`) 
FROM states 
WHERE state_id > (select state_id 
                    FROM states 
                    WHERE entity_id='sensor.weatherstation_ground_temperature' 
                    AND state <> unknown 
                    ORDER BY state_id DESC 
                    OFFSET 0 ROWS FETCH FIRST 100 ROWS ONLY)

If I type this into phpmyadmin, I get the following error:

1064 - There is an error in the syntax used for 'OFFSET 0 ROWS FETCH FIRST 100 ROWS ONLY) LIMIT 0, 25' in line 1.

My question is: what am I doing wrong, or is there a better way?

Answer 1

fetch first does not exist in MySQL; you need limit instead.

SELECT AVG(s.`state`) 
FROM states s
INNER JOIN (
    SELECT state_id 
    FROM states 
    WHERE entity_id='sensor.weatherstation_ground_temperature'  AND state <> 'unknown' 
    ORDER BY state_id DESC 
    LIMIT 100
) x ON x.state_id = s.state_id

Other issues with your query:

• subqueries that use LIMIT cannot be used with the IN operator: you can use a JOIN instead

• state <> unknown should be spelled state <> 'unknown' (this is a syntax error)

Side note: OFFSET 0 is the default, so I can be ignored here.