Last Reading per Month — Solution & ExplanationProblems / Last Reading per Month / EditorialApproachThis is the classic groupwise-maximum problem: the aggregate that defines the winner (MAX(reading_time)) is not the column you need to return (reading_value).

Find each group's winning key. A CTE groups by sensor_id and date_trunc('month', reading_time) and takes MAX(reading_time) — the moment of the last reading in that sensor-month.

Join back for the payload. Joining the CTE to readings on (sensor_id, reading_time) retrieves the row recorded at that moment, and with it reading_value. Because reading times are unique per sensor, the join returns exactly one row per group — no duplicates to clean up.

Cast the month. date_trunc returns a timestamp; CAST(... AS DATE) yields the first-of-month date the output asks for. Order by sensor_id, month.

Key InsightGROUP BY collapses rows, so any non-aggregated column is lost — you cannot ask SQL for "the value *at* the max time" in a single grouped select. The two standard escapes are the max-then-join-back pattern used here and ROW_NUMBER ... ORDER BY reading_time DESC filtered to row 1. The join-back only works cleanly because the winning key is unique within each group (reading times are unique per sensor); when ties are possible, the window-function form with an explicit tiebreaker is the safer tool. Watch the data's traps: the last reading of a month is often *not* its largest value, and a reading at 00:05 on the 1st belongs to the new month, not the old one.Hints

GROUP BY (sensor_id, month) can give you MAX(reading_time), but not the reading_value that goes with it — aggregating value directly (e.g. MAX(reading_value)) answers a different question.

Use date_trunc('month', reading_time) as the month key, then find MAX(reading_time) per (sensor_id, month) in a CTE.

Join that CTE back to readings on sensor_id AND reading_time to retrieve the value recorded at that exact moment. Reading times are unique per sensor, so the join matches exactly one row per group.

A window-function alternative: ROW_NUMBER() OVER (PARTITION BY sensor_id, date_trunc('month', reading_time) ORDER BY reading_time DESC) and keep row 1.

Solve Last Reading per Month yourself →