I have 2 tables - sensors and readings. There is a one to many relation from sensors to readings.
I need to query for all rows from sensors and then get the newest (i.e MAX timestamp) data from readings for each row. I've tried with:
SELECT sensors.*, readings.value, readings.timestamp
LEFT JOIN readings ON readings.sensor_id = sensors.id
GROUP BY readings.sensor_id
This is how I'd go about the problem:
sensor_id unique because I assumed you have one reading per sensor. This can be categorized by types by adding an additional column.
Reason for unique index: we'll be using MySQL's
INSERT INTO ... ON DUPLICATE KEY UPDATE to have all the hard work done for us. If there's a reading for a particular sensor, it gets updated - otherwise, it gets inserted (in one query).
You can also make
sensor_id a foreign key. I skipped that part.
CREATE TABLE latest_readings ( id int unsigned not null auto_increment, sensor_id int unsigned not null, reading_id int unsigned not null, primary key(id), unique (sensor_id) ) ENGINE = InnoDB;
Trigger type is after insert. I will assume that the table is named readings and that it contains
sensor_id column. Adjust accordingly.
DELIMITER $$ CREATE TRIGGER `readings_after_insert` AFTER INSERT ON `readings` FOR EACH ROW BEGIN INSERT INTO readings (sensor_id, reading_id) VALUES (NEW.sensor_id, NEW.id) ON DUPLICATE KEY UPDATE reading_id = NEW.id ; END; $$ DELIMITER ;
Once more, I assumed what column names were, so adjust accordingly.
SELECT r.reading_value FROM readings r INNER JOIN latest_readings latest ON latest.sensor_id = r.sensor_id WHERE r.sensor_id = 12345;
Disclaimer: this is just an example and it probably contains bugs, which means it's not a copy paste solution. If something doesn't work, and it's easy to fix - please do it :)