Johny19 Johny19 - 1 year ago 143
MySQL Question

SQL how to select the most recent timestamp by values?

Let's say I have a table like this

id INT,
sensor INT,
event_type INT,
time datetime

INSERT INTO events VALUES (0,2,4,'2012-06-08 12:13:14');
INSERT INTO events VALUES (1,3,4,'2012-06-08 13:13:14');
INSERT INTO events VALUES (2,2,4,'2012-06-08 12:15:14');
INSERT INTO events VALUES (3,1,6,'2012-06-08 15:13:14');

What is the "best" way to retrieve the most recent event added by sensor? so the result wold be like this (note that id 2 is displayed and not id 0 because id 2 is most recent:

ID sensor
3 1
1 3
2 2

I wrote a select like this, but is there any other way?or simpler way to archive this?

SELECT id,time,sensor,event_type
FROM events s1
WHERE time = (
SELECT MAX(time) FROM events s2 WHERE s1.sensor = s2.sensor


Answer Source

This one variant using GROUP BY, need to run with your data and see the performance

SELECT, events.sensor 
FROM events
    SELECT sensor, max(time) as maxTime
    FROM events
    group by sensor
) T
on events.sensor = T.sensor
and events.time = T.maxTime
ORDER BY events.time DESC
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download