I'm using sensors to get temperature and humidity every X minutes.

I've one table for sensors and one table for their results.





I want to get the latest measurement for every sensor (MAX(date)).

I'm using this:

SELECT id_sens, pos_sens, temp_res, humi_res, MAX(date_res) FROM tb_sensors
INNER JOIN tb_results ON tb_sensors.pk_sens = tb_results.fk_sens
GROUP BY id_sens ORDER BY id_sens

I'm expecting to get:

expected result

but the result is:

querry result

I've got the latest date mixed with the first id, position, temperature and humidity ever written.

How can I resolve this issue?


Most database systems won't even allow the syntax you wrote because it's a little non-sensical from the database engine's perspective. But if I understand you correctly, essentially you want the row with the MAX(date_res) for each sensor.

The way to accomplish this might vary depending on which database system you're using, so you should always tag your question with the database system and version, but here is an answer that should be applicable to most relational database systems:

WITH CTE_Results_Sorted AS
    SELECT fk_sens, temp_res, humi_res, date_res, ROW_NUMBER() OVER (PARTITION BY fk_sens ORDER BY date_res DESC) AS SortId -- Generates a unique ID (assuming each date is unique per sensor) for each sensor result, sorted by date descending
    FROM tb_results

SELECT id_sens, pos_sens, temp_res, humi_res, date_res
FROM tb_sensors
INNER JOIN CTE_Results_Sorted results ON tb_sensors.pk_sens = results.fk_sens
WHERE SortId = 1 -- Filter out everything but the latest date results row per sensor
ORDER BY id_sens
  • Sorry, this was my first post, thank you very much
    – EctoBoggan
    Feb 8 '21 at 14:40
  • @EctoBoggan No problem at all! :)
    – J.D.
    Feb 8 '21 at 15:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.