By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
with
test (val1) as
(select 201900 from dual union all
select 201910 from dual union all
select 201920 from dual union all
select 201930 from dual union all
select 201901 from dual union all
select 201911 from dual union all
select 201921 from dual union all
select 201931 from dual union all
select 201902 from dual union all
select 201912 from dual union all
select 201922 from dual union all
select 201932 from dual),
t as
(select
test.*,
row_number() over (partition by trunc(val1/100), mod(val1, 10)
order by val1) as seq
from test)
select
a.*, b.val1 as prev_value
from t a
left outer join t b
on trunc(a.val1/100) = trunc(b.val1/100) and
mod(a.val1, 10) = mod(b.val1, 10) and
a.seq = b.seq + 1
order by trunc(a.val1/100), mod(a.val1, 10), a.val1;
| VAL1 | SEQ | PREV_VALUE |
|---|---|---|
| 201900 | 1 | null |
| 201910 | 2 | 201900 |
| 201920 | 3 | 201910 |
| 201930 | 4 | 201920 |
| 201901 | 1 | null |
| 201911 | 2 | 201901 |
| 201921 | 3 | 201911 |
| 201931 | 4 | 201921 |
| 201902 | 1 | null |
| 201912 | 2 | 201902 |
| 201922 | 3 | 201912 |
| 201932 | 4 | 201922 |
with test (val1) as
(select 201900 from dual union all
select 201910 from dual union all
select 201920 from dual union all
select 201930 from dual union all
select 201901 from dual union all
select 201911 from dual union all
select 201921 from dual union all
select 201931 from dual union all
select 201902 from dual union all
select 201912 from dual union all
select 201922 from dual union all
select 201932 from dual)
select val1,
lag(val1) over (partition by trunc(val1/100), mod(val1, 10)
order by val1) as prev_value
from test
order by trunc(val1/100), mod(val1, 10), val1;
| VAL1 | PREV_VALUE |
|---|---|
| 201900 | null |
| 201910 | 201900 |
| 201920 | 201910 |
| 201930 | 201920 |
| 201901 | null |
| 201911 | 201901 |
| 201921 | 201911 |
| 201931 | 201921 |
| 201902 | null |
| 201912 | 201902 |
| 201922 | 201912 |
| 201932 | 201922 |