Wednesday, June 15, 2011

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

No comments:

Post a Comment