Johny19 Johny19 - 5 months ago 49
MySQL Question

SQL how to select the most recent timestamp by values?

Let's say I have a table like this

CREATE TABLE events (
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
)
ORDER BY time DESC


THanks!

Answer

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

SELECT events.id, events.sensor 
FROM events
JOIN
(
    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
Comments