# How to calculate value difference of two rows using SELECT query only

I have the following table named ABC_table:

ID | value
-----------
1 | 10
2 | 13
3 | 18
4 | 20
5 | 24

I would like to create an SELECT SQL query that would calculate difference of values. The result should be:

ID | value | differ
----------------------
1 | 10 | NULL
2 | 13 | 3
3 | 18 | 5
4 | 20 | 2
5 | 24 | 4

The differ is calculated between two rows. So, column differ in row with ID=2 is calculated like that: value of row with ID=2 which is 13 MINUS value of row with ID=1 which is 10. Result is 3.

I know that could be implemented using two recordsets. Instead of that, I would prefer one SELECT query to do that. Any suggestions are appreciated.
###### Who is Participating?

Software EngineerCommented:
If there is one primary key column -( incremental number), things would be easy.

Otherwise you need to take advantage of an identity column like this
``````declare @table table (rank_no int identity(1,1), ID int, value int)
insert into @table select * from #table
select ID, value, t.value - (select max(value) from @table a where a.rank_no < t.rank_no) [Difference]
from @table t
``````

Result
``````ID	value	Difference
18	10	NULL
12	13	3
33	18	5
4	20	2
50	24	4
``````
0

Software EngineerCommented:
``````select *, t.value - (select max(value) from #table a where a.ID < t.ID) [Difference]
from yourtable t
``````
0

Software EngineerCommented:
One Correction
``````select *, t.value - (select max(value) from yourtable a where a.ID < t.ID) [Difference]
from yourtable t
``````

``````ID	value	Difference
1	10	NULL
2	13	3
3	18	5
4	20	2
5	24	4
``````
0

Author Commented:
True, that works if ID increases all the time by step 1. What about if my table looks like this:

ID | value
-----------
18 | 10
12 | 13
33 | 18
4   | 20
50 | 24

And I would like to get the same result using one SELECT query:

ID | value | differ
----------------------
18 | 10 | NULL
12 | 13 | 3
33 | 18 | 5
4   | 20 | 2
50 | 24 | 4
0

Billing EngineerCommented: