CODEWITHSUNDEEP
mysqlsqlgroup-bytimestamp
Johny19
Johny19

Reputation: 5582

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!

Upvotes: 1

Views: 6390

Answers (3)

Nikhil Gupte
Nikhil Gupte

Reputation: 3326

This works without using an aggregate function:

select e1.* from events e1 
  left outer join events e2 on e1.sensor = e2.sensor 
    and e1.id != e2.id 
    and e1.time < e2.time 
  where e2.time is null;

Upvotes: 0

shitikantha subham patro
shitikantha subham patro

Reputation: 31

select * from events 
     where time in(SELECT max(time) FROM events 
            group by sensor_id,event_type) 
     Order by time desc;

Upvotes: 3

radar
radar

Reputation: 13425

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

Upvotes: 3

Related Questions