Re: [sqlite] How to get the previous row before rows matching a where clause...

[Jim Wilcoxson]

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  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

Re: [sqlite] How to get the previous row before rows matching a where clause...

[Dennis Cote]

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

Re: [sqlite] How to get the previous row before rows matching a where clause...

[P Kishor]

On Sun, Mar 22, 2009 at 4:23 PM, 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.
>

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

[sqlite] How to get the previous row before rows matching a where clause...

[sorka]

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