Take the following example code:
with test as (
select to_date('01/2012', 'mm/yyyy') as dt, '1' as value from dual union all
select to_date('02/2012', 'mm/yyyy') as dt, '10' as value from dual union all
select to_date('03/2012', 'mm/yyyy') as dt, '100' as value from dual union all
select to_date('04/2012', 'mm/yyyy') as dt, '2' as value from dual union all
select to_date('05/2012', 'mm/yyyy') as dt, '20' as value from dual
)
select dt, value from test
which returns:
DT | VALUE
1/1/2012 | 1
2/1/2012 | 10
3/1/2012 | 100
4/1/2012 | 2
5/1/2012 | 20
I wish I could build a new column containing the previous value of each record, such as:
DT | VALUE | Previous
1/1/2012 | 1 | -
2/1/2012 | 10 | 1
3/1/2012 | 100 | 10
4/1/2012 | 2 | 100
5/1/2012 | 20 | 2
Which seems simple enough though I constantly get lost with connect_by statements.
could someone help?
No need to use a CONNECT BY. You just need a LAG
SQL> ed
Wrote file afiedt.buf
1 with test as (
2 select to_date('01/2012', 'mm/yyyy') as dt, '1' as value from dual union all
3 select to_date('02/2012', 'mm/yyyy') as dt, '10' as value from dual union all
4 select to_date('03/2012', 'mm/yyyy') as dt, '100' as value from dual union all
5 select to_date('04/2012', 'mm/yyyy') as dt, '2' as value from dual union all
6 select to_date('05/2012', 'mm/yyyy') as dt, '20' as value from dual
7 )
8 select dt,
9 value,
10 lag(value) over (order by dt) prior_value
11* from test
SQL> /
DT VAL PRI
--------- --- ---
01-JAN-12 1
01-FEB-12 10 1
01-MAR-12 100 10
01-APR-12 2 100
01-MAY-12 20 2