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.


-- 
View this message in context: 
http://www.nabble.com/How-to-get-the-previous-row-before-rows-matching-a-where-clause...-tp22650799p22650799.html
Sent from the SQLite mailing list archive at Nabble.com.

_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to