sql difference in same column from bottom to top value [closed]

Question

table :

a
1
4
8
9

desired output

a
3
4
1

Answer 1

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;

Fiddle

Answer 2

I am using the How can I subtract two row's values within same column using sql query? logic.

1. First we will give row number to your table

   Select ROW_NUMBER() over(order by a) as rn, a
    From tb1
   

   Output:

2. 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:

3. 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