Home » SQL & PL/SQL » SQL & PL/SQL » Calculating values from previous record on different column (Oracle 10g, Unix/Linux)
Calculating values from previous record on different column [message #404188] Wed, 20 May 2009 13:01 Go to next message
Jae Bosou
Messages: 7
Registered: December 2003
Junior Member
I have a situation where I need to do a calculation based on the previous value on a different column. In SQL or PLSQL, what would the script like to update the values in columns shown below:

This example is for one id with multiple seqno:
----------------------------------------
id      seqno   column1 column2 column3
----------------------------------------
123     1       2       0       2
123     2       2       1       2
123     3       2       3       2
123     4       2       2       2

to the result below:

----------------------------------------
id      seqno   column1 column2 column3
----------------------------------------
123     1       2       0       2
123     2       2       1       3
123     3       3       3       6
123     4       6       2       8

Notice that column3 got updated from the result column1 + column2. And then, the next record in column1 got updated from the previous seqno in column3.

You help is appreciated.

Thank you

JB

[Updated on: Wed, 20 May 2009 13:03] by Moderator

Report message to a moderator

Re: Calculating values from previous record on different column [message #404190 is a reply to message #404188] Wed, 20 May 2009 13:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a working Test case: create table and insert statements along with the result you want with these data.

Also please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: Calculating values from previous record on different column [message #404327 is a reply to message #404188] Thu, 21 May 2009 06:33 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Setting column3 to column_1 + column_2 is trivial.

For the other part, you need to look at the
LAG function
Previous Topic: How to Output the updated row
Next Topic: Need to optimize query
Goto Forum:
  


Current Time: Thu Dec 08 04:32:14 CST 2016

Total time taken to generate the page: 0.09486 seconds