How find the difference between two consecutive rows in a table ?

By using a self join , as below.
Note that this query assumes the table has an autoincrement column like ID . If it is not, then possibly it will have to generated using ROW_NUMBER. Note that using autoincrement is better than using something like TOP.



create table test ( ID int, val int) insert test( ID , val) select 1, 10union all select 2, 12union all select 3, 9union all select 4, 15union all select 5, 8union all select

 select



select

ID val
1 10
2 12
3 9
4 15
5 8
6 20



select a.ID , b.ID , a.val , b.val , a.val - b.val as diff from test a left join test b on


ID ID val val diff
1 NULL 10 NULL NULL
2 1 12 10 2
3 2 9 12 -3
4 3 15 9 6
5 4 8 15 -7
6 5 20 8 12 b.ID = a.ID -1 * from test a.ID , b.ID , a.val , b.val , a.val - b.val as diff from test a left join test b on b.ID = a.ID -1 6, 20