Home Sqlite checking if value in one row is between values in next rows without loop

Questions

Sqlite checking if value in one row is between values in next rows without loop

December 19, 2021

Let’s say that I have sql table like this:

id |  val_1 | val_2 | 
1  |  50    |  130  | -
2  |  70    |  110  | False
3  |  60    |  135  | True
4  |  40    |  70   | True

...

Now, I want to check if (val_1 50 is between 70 AND 110) OR ( val_2 130 is between 70 and 110), which gives False, and the next row which gives True coz 130 is between 60 and 135 and so on. Is that possible with window functions?

>Solution :

You can use the lag function to get the previous row:

SELECT id, val_1, val_2,
       LAG(val_1) OVER (ORDER BY id ASC) BETWEEN val_1 AND val_2 OR
       LAG(val_2) OVER (ORDER BY id ASC) BETWEEN val_1 AND val_2
FROM   mytable