How to find last values in mySQL tables sorted by datetime?

I have a problem finding the right values from mySQL Database.

I have 1 table with 3 main columns:

---+------------+---------------+--------------------+
id + patient    + temperature   +  datetime          +
-- +------------+---------------+--------------------+
1  + patient #1 + 36.6C/97.88F  + 31-12-2018 12-00-00

I need as a result a table with all patients and just one value for every patient:

--------+-------------------------------+-----------------------------+
patient + last temperature in this year + last datetime in this year  +
--------+-------------------------------+-----------------------------+

Answer

You need the last value of datetime this year:

SELECT 
  id, 
  temperature, 
  datetime 
FROM 
  patients AS p
WHERE 
  datetime = (
    SELECT MAX(datetime) FROM patients WHERE patients.id = p.id AND YEAR(datetime) = YEAR(CURDATE())
  )

Advertisement

Answer