Solved

carry forward row values

Posted on 2011-02-22
7
1,149 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 74

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 74

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 74

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 74

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

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Create Index on a Materialized View 5 36
dbms_crypto.decrypt   errors out 6 35
pl/sql - query very slow 26 74
why truncate is faster than delete in oracle ? 4 28
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to take different types of Oracle backups using RMAN.

820 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