Solved

carry forward row values

Posted on 2011-02-22
7
1,210 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

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…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to recover a database from a user managed backup
Suggested Courses

624 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