sorka wrote: > I have a table of events that have a title, start time, and end time. > > The start time is guaranteed unique, so I've made it my primary integer key. > > I need all events that overlap the a window of time between say windowstart > and windowend. Currently, the statement > > SELECT title FROM event WHERE startTime < windowEnd AND endTime > > windowStart. > > I've indexed the end time and the query is pretty fast, but it could be a > lot faster if I only had to use the integer primary key. > > If instead I do > > SELECT title from event WHERE startTime > windowStart AND startTime < > windowEnd > > this will get me almost the same thing except that it will be missing the > first event that overlaps the windowStart because it's startTime is at or > before startTime. > > In this case, if I can get exactly the previous row added to what is > returned in the results above, I'll have exactly what I need. > > So the question is, when a WHERE clause returns a set of rows, is there a > way to also get the row at the ROWID that comes just before the row that is > returned from above with the lowest ROWID? > > Another way of putting it, if I take the lowest ROWID that is returned in my > second example and get the next lowest ROW, the one that is less than the > lowest ROWID I got but closest to it .i.e, the one right before it, then it > would be complete. > > > This should give you the result you want, all your current results and the row with the immediately prior starttime.
select title from event where starttime > windowstart and starttime < windowend union select title from event where starttime = (select max(starttime) from event where starttime <= windowstart ); This is an alternate version that should perform better in case the optimizer doesn't optimize the max() function. select title from event where starttime > windowstart and starttime < windowend union select title from event where starttime = (select starttime from event where starttime <= windowstart order by starttime desc limit 1); HTH Dennis Cote _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users