Selecting the last event for a period of time

Question

MySQL

Good afternoon! Help (if possible) to make a request.
There is a table with events and objects:

id	object_id	time	type_event
one	one	2013-03-25 02:00:02	one
2	2	2013-03-25 02:30:22	one
3	one	2013-03-25 02:00:12	0
4	2	2013-03-25 02:50:22	0
5	one	2013-03-25 02:45:22	one

you need to get the last event for the specified period of time for the object, I tried to sort by time and group by object but it didn’t work ... (the result should be record 5 for object 1, and record 4 for object 2)

Answer 1

If I understand correctly, you need to select all the dates in one query.

SELECT id, MAX(time) FROM table_name
WHERE time BETWEEN '2013-01-01' AND '2014-01-01'
GROUP BY id

Answer 2

select * from tablename t1,
(
   select object_id, max(time) as max_time from tablename 
   group by object_id
) as t2
where 
t1.object_id = t2.object_id
and t1.time = t2.max_time

Answer 3

If I understand the question correctly, then in
SELECT id FROM table_name WHERE object_id = $OBJECT_ID AND time >= $START_TIME AND time <= $END_TIME ORDER BY time LIMIT 1
order for the query to be efficient, you may need (if the table is large) a composite index on object_id and time.
Most likely, time will have to be quoted, depending on what kind of DBMS.

Answer 4

Depending on the sample size, it may make sense to do it through a temporary table with a unique index:

create temporary table tablename_uniq like tablename;
alter table tablename_uniq 
  add column record_date date, 
  add unique index (object_id, record_date);
insert ignore into tablename_uniq 
  select id, object_id, time, type_event, cast(time as date) 
  from tablename 
  /* where time between @range_start and @range_end */ 
  order by time desc;
select id, object_id, time, type_event from tablename_uniq;
drop temporary table tablename_uniq;

Answer 5

select * from event as t1
where time = (select max(time) from event where object_id = t1.object_id group by object_id)