how to get the last row value from resultset and add it with next set of rows in the resultset

Question

4.50/5 (2 votes)

See more:

SQL-Server-2008

Hi all,

How can i implement this following logic in sql server :

I have 3 columns in a table say :

Distance  Height   Sequence
0.91      10        1
0.98      11        1
1.2       12        1
0         13        2
0.81      15        2
0.98      17        2
1.6       19        3
2.1       19.5      3

logic is as follows:
i should set offset value as 0.0
when new sequence starts, i should add offset value with previous row sequence value

eg:
set offset=0.0
offset=0.0+1.2(previous row value)=1.2

i should add the above offset value for values having sequence numbers as 2.
output for sequence having 2 will be :

Distance  Height   Sequence
1.2       13        2
2.01      15        2
2.18      17        2

now offset=1.2+2.18(previous row value)=3.38
above offset value, i should add for sequence numbers as 3

Distance  Height   Sequence
4.98       19        3
5.48       19.5      3

Overall my table should return the following output :

Distance  Height   Sequence
0.91      10        1
0.98      11        1
1.2       12        1
1.2       13        2
2.01      15        2
2.18      17        2
4.98      19        3
5.48      19.5      3

Thanks in advance.

Posted 19-Apr-12 15:22pm

DepiyaReddy

Updated 19-Apr-12 16:33pm

Chris Maunder774.3K

v2

Add a Solution

Comments

milenalukic 22-Apr-12 19:12pm

I cannot understand you mathematical formula and how you are geting your results but if you need to get previous row in SQL you need to have an identity column of type int. This will give you a row number. Once you add this to your data and you select a row you need to declare a variable @previousrow of type int and assign it to your ID value -1. Then select the row with ID=@previousrow

DepiyaReddy 22-Apr-12 19:36pm

Considering only Distance and Sequence columns for the logic:
In my above example ,when my new sequence id starts,i should get the previous rowvalue(of Distance column ) and add to the rows starting with new sequence .
Im setting a variable value :offset =0.0
So when my Sequence "2" starts,I should get the prev row value of distance (whose sequence was 1)and add to offset .
Last row value of sequence "1" in this eg is 1.2
so Offset=0.0+1.2=1.2
I will be adding 1.2 to rows having sequence 2.Thus the output
Distance Height Sequence
0.91 10 1
0.98 11 1
1.2 12 1
1.2 13 2
2.01 15 2
2.18 17 2
Now following the same logic for sequence 3:
Now last row value(distance column) of sequence 2 is 2.18
offset=1.2+2.18=3.38
Adding the above offset value to the rows having sequence as 3,output would be :
Distance Height Sequence
0.91 10 1
0.98 11 1
1.2 12 1
1.2 13 2
2.01 15 2
2.18 17 2
4.98 19 3
5.48 19.5 3

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dec90 · Answer 1 · 2012-11-05T05:22:00

Solution 1

are you looking for a recursive CTE?

http://msdn.microsoft.com/en-us/library/ms186243(v=sql.105).aspx

Posted 5-Nov-12 5:22am

Dec90