P Kishor-3 wrote:
>
> 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
>
>
Actually, the next lowest ROWID that is less than windowStart is the row
that I need.
The statement:
SELECT max(ROWID) FROM event WHERE ROWID < windowStart;
works just fine, but the performance is lousy.
--
View this message in context:
http://www.nabble.com/How-to-get-the-previous-row-before-rows-matching-a-where-clause...-tp22650799p22652147.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
