sql difference in same column from bottom to top value-CodePudding

table :

a
1
4
8
9

desired output

a
3
4
1

CodePudding user response：

You can use lead to get the value of a from the next row (ordered by a):

select * from 
(select lead(a) over(order by a) - a as a
from table_name) t
where a is not null;

We will create a temp table where the first row will be missing and the row number will adjust like that:
```
Select ROW_NUMBER() over(order by a) as rn, cte.a
From cte
Where cte.rn != 1
```

Output:

Finally will just join the table using the rn and substruct the value

With cte as(
  Select ROW_NUMBER() over(order by a) as rn, cte.a
  From cte
  Where cte.rn != 1
)
cte_2 as(
  Select ROW_NUMBER() over(order by a) as rn, cte.a
  From cte
  Where cte.rn != 1
 )
  Select ABS(cte.a - cte_2.a) as sub
  From cte join cte_2
  on cte.rn = cte_2.rn

Output