Shantanu Oak (![[info]](http://l-stat.livejournal.com/img/userinfo.gif){kind=small} shantanuo) wrote, |
Correct Query
select * from events e1
where id in (
select id from events e2 join
(select datetime('now', '+1 day','start of day', '+9 hours','+30
minutes') x)
where e1.name = e2.name and start < x and end > x
order by (julianday(end) - julianday(start)) limit 1
);
The variant below is more complicated, but may run faster:
select * from events e1
where id in (
select (
select id from events e2
where e2.name = names.name and start < x and end > x
order by (julianday(end) - julianday(start)) limit 1
)
from (select distinct name from events) names join
(select datetime('now', '+1 day','start of day', '+9 hours','+30
minutes') x)
);
select * from events e1
where id in (
select id from events e2 join
(select datetime('now', '+1 day','start of day', '+9 hours','+30
minutes') x)
where e1.name = e2.name and start < x and end > x
order by (julianday(end) - julianday(start)) limit 1
);
The variant below is more complicated, but may run faster:
select * from events e1
where id in (
select (
select id from events e2
where e2.name = names.name and start < x and end > x
order by (julianday(end) - julianday(start)) limit 1
)
from (select distinct name from events) names join
(select datetime('now', '+1 day','start of day', '+9 hours','+30
minutes') x)
);
