[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
5
Medium Priority
?
188 Views
Last Modified: 2012-05-12
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
Comment
Question by:JanjaNovak
  • 3
5 Comments
 
LVL 23

Expert Comment

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

Open in new window

0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 37011460
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
 

Author Comment

by:JanjaNovak
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

by:
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

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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 37017748
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question