I have been struggling with this query for days. I have three tables: deployment, messages, status_messages. Each deployment has many messages and a subset of those are status messages: deployment: id, unit_id message: id, deployment_id, time status: message_id, battery_volatage, latitude, longitude
I would like to see fields from the most recent status message for each deployment. I have tried multiple approaches along the lines of:
SELECT d.id, d.unit_id, MAX(m.time) as msg_time,
s.latitude, s.longitude, s.battery_voltage
FROM status as s
JOIN message AS m ON m.id = s.message_id
JOIN deployment AS d ON d.id = m.deployment_id
GROUP BY msg_time;
`
With results: Error Code: 1055. Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mydb.d.id' which is not functionally dependent on columns in GROUP BY clause;
I would be so deeply grateful if someone could point me in the right direction!
