Solved

carry forward row values

Posted on 2011-02-22
7
1,121 Views
Last Modified: 2012-06-22
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?
Thanks in advance.
0
Comment
Question by:subratoc
  • 4
  • 3
7 Comments
 
LVL 73

Expert Comment

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

Accepted Solution

by:
sdstuber earned 250 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

by:subratoc
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

Expert Comment

by:sdstuber
ID: 34954106
see above
0
 

Author Comment

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

Expert Comment

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

Author Comment

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

867 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now