Consider the table
ID Value
1 3
2 4
3 6
4 7
5 8
6 11
7 12
I want to find rows where the difference between two subsequent Values is different than 1.
Id = 2 has Value = 4 and Id = 3 has Value = 6, difference in Value between these two rows is 2, which is different than 1. As a return I want this table
Id Difference
2 2
5 3
I am using MySQL (SQL available at cPanel).
I could not find a solution to this problem anywhere.
>Solution :
This is easily solved by using lead or lag windows:
with diff as (
select * , Abs(value - lead(value) over(order by id)) diff
from t
)
select *
from diff
where diff > 1;
See Fiddle