English is currently available.
SQL DISCUSSION
A readings table stores sensor_id, reading_time and value. I want the most recent reading of every sensor. SELECT sensor_id, MAX(reading_time), value FROM readings GROUP BY sensor_id fails with an error saying value must appear in the GROUP BY clause or be used in an aggregate function. On an old MySQL server the same query runs but the values are sometimes from the wrong row.
Why is the query rejected, and what is the correct way to get the whole latest row per sensor?
GROUP BY collapses each group into a single output row. For sensor_id that is well defined, and MAX(reading_time) computes one value per group, but a sensor with 500 readings has 500 candidates for value and nothing in the query says which to show. Standard SQL therefore rejects any selected column that is neither grouped nor aggregated.
MySQL used to allow it when the ONLY_FULL_GROUP_BY mode was off and returned a value from an arbitrary row of the group, which is exactly the wrong-row symptom you saw. MAX(reading_time) does not drag the rest of its row along with it.
The most readable solution uses a window function. Number the rows inside each sensor, newest first, and keep number 1: SELECT sensor_id, reading_time, value FROM (SELECT r.*, ROW_NUMBER() OVER (PARTITION BY sensor_id ORDER BY reading_time DESC) AS rn FROM readings r) t WHERE rn = 1.
Window functions are available in PostgreSQL, SQL Server, Oracle, SQLite and MySQL 8. Changing the filter to rn <= 3 gives the latest three readings per sensor with no other change.
The classic alternative, which works on any version, is to compute the maximum per group and join it back: SELECT r.* FROM readings r JOIN (SELECT sensor_id, MAX(reading_time) AS mt FROM readings GROUP BY sensor_id) m ON m.sensor_id = r.sensor_id AND m.mt = r.reading_time.
Mind the ties. If a sensor has two rows with the same latest timestamp, this version returns both, while ROW_NUMBER returns one of them, chosen arbitrarily unless you add a tie-breaker such as ORDER BY reading_time DESC, id DESC. Decide which behaviour you want.
Either form becomes fast with a composite index on (sensor_id, reading_time); without it the database sorts or scans the whole table for every run.
While you are in GROUP BY territory, keep WHERE and HAVING apart. WHERE filters rows before grouping and cannot contain aggregates; HAVING filters the groups afterwards. A query with WHERE reading_time >= '2026-09-01', then GROUP BY sensor_id, then HAVING COUNT(*) >= 10 first restricts to September onwards and then keeps only sensors with at least 10 readings in that period. Put every condition that does not need an aggregate into WHERE so fewer rows are grouped.
