What about:
select blah from blah where rowid < windowstartrowid order by
rowid desc limit 1
to get the row before, and:
select blah from blah where rowid > windowlastrowid limit 1
to get the row after.
Jim
On 3/22/09, 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.
>
>
> --
> 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
>
--
Software first. Software lasts!
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
