Select only neighbour rows who pass a where-test I want to apply a selection process to my data, that will not include rows which should be accepted by my WHERE clause but who follow after a row which fails the WHERE.

Example. I have a list of gps locations and want to put them in a tour entity, if they surpass a speed limit.

SELECT _id, speed FROM location;


results in (sqlite3 syntax):

1|0
2|0
3|3
4|3
5|3
6|0
7|0
8|0
9|3
10|3
11|3
12|0


So, if I SELECT for WHERE speed &gt; 2 now, I will get 3,4,5 and also 9,10,11. These both clearly should be separated tours. Can I formulate my SQL in a way that it only results in 3,4,5 because the following entity will result in a failed WHERE speed &gt; 2 test?

First I thought, that I can simply return all these entities, but now I think there will be 2 problems with that. First there might be a lot of these, when this SELECT is called. And second there might be deleted rows. So instead of the 11 row there might be a lot higher number as _id which still should belong to the same tour, because there are no rows between 10 and this row and both pass the WHERE speed &gt; 2. That there are no rows failing the WHERE between 10 and the next row is totally unknown the Application waiting for the result of this SELECT.

It is on purpose that I don't ask for a database specific answer. If you know an easy solution for database X or Y there is no problem adding it as a solution. The real question is, how to solve it with standard SQL, though. I think every well known database should have some way of solving any task out of the standard solvable task set.
I don't know if this will work in sqlite3, but you didn't tag the question with that, so here is an Oracle solution.  

SELECT ID, Speed FROM
(
    SELECT ID, Speed, MIN(Speed) OVER (ORDER BY ID) InSequence  
    FROM
    (
        SELECT ID, Speed, SUM(Speed) OVER (ORDER BY ID) SpeedSum
        FROM location 
    )
    WHERE SpeedSum &gt; 0 
) 
WHERE InSequence &gt; 0
ORDER BY ID;


The SUM(Speed) analytic removed the leading zero speed rows and the MIN(Speed) analytic allows us to eliminate rows starting with the first zero speed row remaining.  The sample data can be created as follows:

drop table location;

create table location as (select level id, 
   case when level between 3 and 5 then 3 
        when level between 9 and 11 then 3
        else 0 end speed from dual connect by level &lt;=12);

select * from location;


It seems to behave correctly through all the following variations, but there could be a pattern I've missed:

UPDATE location SET Speed=1 WHERE ID=2;
UPDATE location SET Speed=2 WHERE ID=3;
UPDATE location SET Speed=1 WHERE ID=6;
DELETE FROM location WHERE ID IN (7,9);


UPDATE location SET Speed=0 WHERE ID=2;
UPDATE location SET Speed=5 WHERE ID=1;


This solution only requires one full table scan rather than the two Noam's solution requires, but it only finds the first set of values whereas Noam's finds all the sets.  It also assumes a threshold of zero, but could be modified for any threshold.