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
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
WHERE speed > 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 > 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 > 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
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.