Assuming, in your example, that the ordering you show is given in table t by a sequential id column...
id,value
~~~~~~~~
1, 100
2, 120
3, 140
4, 110
5, 130
6, 105
7, 110
8, 80
Then we find value-successor pairs with ...
select a.value, b.value as successor
from t as a
join t as b on b.id=a.id+1
order by a.id;
...so the pairs and diffs where value > successor are...
select value, successor, value-successor as diff
from (
select a.value,b.value as successor
from t a
join t b on b.id=a.id+1
order by a.id
) p
where value > successor;
If id isn't sequential, use something like this as the subquery to find successors...
select value, (select min(value) from parent p where p.id>parent.id) as successor
from parent
order by id;
For other ways to find previous/next rows see "Find previous and next values in a sequence" at
http://www.artfulsoftware.com/queries.php
Edited 1 time(s). Last edit at 04/17/2015 10:23AM by Peter Brawley.