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.
JanjaNovakAsked:
Who is Participating?
 
Rajkumar GsConnect With a Mentor 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

Open in new window


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

Open in new window

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

Open in new window

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

Open in new window


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

Open in new window

0
 
JanjaNovakAuthor 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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please see this article to ensure you (re)write the most optimal SQL for MySQL for the "lead"/"lag" values:
http://explainextended.com/2009/03/12/analytic-functions-optimizing-lag-lead-first_value-last_value/
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.