To retrieve each person’s value at two historical cutoffs, first find the latest qualifying timestamp for each cutoff in one grouped pass. Then join those timestamps back to the readings table to obtain the corresponding values.
Last updated: October 3, 2026.
WITH cutoff_dates AS (
SELECT
person_id,
MAX(CASE WHEN recorded_at <= '2026-01-01' THEN recorded_at END) AS first_date,
MAX(CASE WHEN recorded_at <= '2026-05-05' THEN recorded_at END) AS second_date
FROM readings
WHERE recorded_at <= '2026-05-05'
GROUP BY person_id
)
SELECT
c.person_id,
r1.value AS value_at_first_date,
r2.value AS value_at_second_date
FROM cutoff_dates AS c
LEFT JOIN readings AS r1
ON r1.person_id = c.person_id
AND r1.recorded_at = c.first_date
LEFT JOIN readings AS r2
ON r2.person_id = c.person_id
AND r2.recorded_at = c.second_date
ORDER BY c.person_id;The conditional MAX() expressions return the last timestamp that does not exceed each cutoff. A left join preserves a person even when no reading exists before one of the dates, producing NULL for that value.
Make the timestamp identify one row
The query assumes that (person_id, recorded_at) is unique. If multiple readings may share a timestamp, add a stable tiebreaker such as reading_id and define which row wins. Without that rule, joining on the timestamp can return duplicates.
MySQL documents the behavior of conditional MAX() in its aggregate-function reference. The WITH reference explains that the CTE names a temporary result available to the main statement.
Support the access pattern with one index
Create an index beginning with (person_id, recorded_at). It supports grouping by person, date range checks, and both joins back to the readings. Confirm the actual plan and row counts rather than adding separate single-column indexes automatically. MySQL’s index guidance describes how composite B-tree keys support lookups and MIN()/MAX() access.
For related patterns, see SQL GROUP BY, SQL ORDER BY, and choosing MySQL indexes for filter patterns.
