On Sun, Mar 22, 2009 at 4:23 PM, sorka <sorka95...@gmail.com> 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. >
if you know the "lowest rowid that is returned from your query" the next lowest rowid would be SELECT rowid FROM table WHERE rowid = (SELECT Max(rowid) FROM (SELECT rowid FROM table WHERE query)); -- Puneet Kishor http://www.punkish.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Carbon Model http://carbonmodel.org/ Open Source Geospatial Foundation http://www.osgeo.org/ Sent from: Madison WI United States. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
