[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
Solved

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

Posted on 2011-10-22
Medium Priority
188 Views
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.
0
Question by:JanjaNovak
• 3

LVL 23

Expert Comment

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

LVL 23

Expert Comment

ID: 37011460
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 Comment

ID: 37011556
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

LVL 23

Accepted Solution

Rajkumar Gs earned 2000 total points
ID: 37011602
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

LVL 143

Expert Comment

ID: 37017748
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
###### Suggested Courses
Course of the Month18 days, left to enroll