Allen Fowler wrote: > # Show all events and duration: > sqlite> select *, (strftime('%s', end) - strftime('%s', start)) as > length from events; > id name kind > start end length > -------------------- -------------------- -------------------- > -------------------- -------------------- ---------- 1 > tom hour 2009-06-13 11:00:00 > 2009-06-13 12:00:00 3600 2 tom > hour 2009-06-13 09:00:00 2009-06-13 10:00:00 > 3600 3 joe hour > 2009-06-13 09:00:00 2009-06-13 10:00:00 3600 4 > tom day 2009-06-13 00:00:00 > 2009-06-14 00:00:00 86400 > > > # > # And now, I want to get a result table with one row per user showing > the "shortest active at 9:30 AM event" for each user.
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) ); Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users