Sqlite checking for cateory without loop-CodePudding

let's say that I have table like this:

id ; cat ; val_2 ; val_3
1  ;  1    ;  40   ;  80
2  ;  1    ;  60   ;  90
3  ;  2    ;  10   ;  120
4  ;  2    ;  30   ;  100
5  ;  3    ;  20   ;  40
5  ;  3    ;  40   ;  70
5  ;  3    ;  50   ;  60

Now, I need to do the operation for each category cat, but not like sum with group by, rather I need check for every row is val_2 between val_2 and val_3 in next row. So:

(LAG(val_2) OVER() BETWEEN val_2 AND val_3) OR
(LEAD(val_2) OVER() BETWEEN val_2 AND val_3)

But how to do it with each category separately?

CodePudding user response：

You could add a partition by clause:

(LAG(val_2) OVER(PARTITION BY cat ORDER BY id) BETWEEN val_2 AND val_3) OR
(LEAD(val_2) OVER(PARTITION BY cat ORDER BY id) BETWEEN val_2 AND val_3)