Question 296467 in cPanel Question posted in
The official documentation can be seen here.

Cpanel – How to find rows that have specific difference between two rows

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.

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    This also gives the solution without using windows function like ‘LAG’

     SELECT v1. id, v2.value - v1.value AS difference
 FROM Mytable v1 JOIN MyTable v2 ON v1.id = (v2.id)-1
 WHERE ABS(v1.value - v2.value) <> 1;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search