[Webinar] Streamline your web hosting managementRegister Today

x
Solved

# carry forward row values

Posted on 2011-02-22
Medium Priority
1,300 Views
I have got table with 10 rows and one column:
0
0
1
0
0
0
2
0
0
0

I want to write a select query which will carry forward any non-zero values to successive rows unless the value in the column changes.

This means that the query will return the following for the data shown above:
0   0
0   0
1   1   --value changed from 0 to 1 in column 1
0   1  --value '1' carried forward as the current value of column1 is zero
0   1  --value '1' carried forward as the current value of column1 is zero
0   1  --value '1' carried forward as the current value of column1 is zero
2   2  --value '1' not carried forward as the current value of column1 is 2
0   2  --value '1' carried forward as the current value of column1 is zero
0   2  --value '1' carried forward as the current value of column1 is zero
0   2  --value '1' carried forward as the current value of column1 is zero

Any idea anybody?
0
Question by:subratoc
• 4
• 3

LVL 74

Expert Comment

ID: 34954047
you must have a second column in order to ensure the sorting order you have displayed.

otherwise there is no guarantee the rows will be sorted, and hence carried forward the same way.
0

LVL 74

Accepted Solution

sdstuber earned 1000 total points
ID: 34954081
given a second column called "idx" that determins the ordering, you can try something like this...

SELECT   col,
NVL(
LAST_VALUE(NULLIF(col, 0) IGNORE NULLS)
OVER (ORDER BY idx ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
col)
carry
FROM yourdata
ORDER BY idx
0

Author Comment

ID: 34954092
OK. Let's say I have another column (not shown above) which ensures the order of the rows.
This revises the question to:
The query shouldreturn the following for the data shown above:
0   0
0   0
1   1   --value changed from 0 to 1 in column 1
0   1  --value '1' carried forward as the current value of column1 is zero
0   1  --value '1' carried forward as the current value of column1 is zero
0   1  --value '1' carried forward as the current value of column1 is zero
2   2  --value '1' not carried forward as the current value of column1 is 2
0   2  --value '2' carried forward as the current value of column1 is zero
0   2  --value '2' carried forward as the current value of column1 is zero
0   2  --value '2' carried forward as the current value of column1 is zero
0

LVL 74

Expert Comment

ID: 34954106
see above
0

Author Comment

ID: 34954144
Please ignore the last post. Revised question is:
OK. Let's say I have another column (not shown above) which ensures the order of the rows.
This revises the question to:
The query shouldreturn the following for the data shown above:

0   0  1
0   0  2
1   1  3    --value changed from 0 to 1 in column 1
0   1  4   --value '1' carried forward as the current value of column1 is zero
0   1  5   --value '1' carried forward as the current value of column1 is zero
0   1  6   --value '1' carried forward as the current value of column1 is zero
2   2  7   --value '1' not carried forward as the current value of column1 is 2
0   2  8   --value '2' carried forward as the current value of column1 is zero
0   2  9   --value '2' carried forward as the current value of column1 is zero
0   2  10  --value '2' carried forward as the current value of column1 is zero
Here I am ensuring the order using the third column (1-10).
0

LVL 74

Expert Comment

ID: 34954169
same as before except include the ordering column
ee.txt
0

Author Comment

ID: 34954227
Excellent! Thanks a ton. You are a genius indeed.
0

## Featured Post

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to recover a database from a user managed backup
###### Suggested Courses
Course of the Month9 days, 17 hours left to enroll

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

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