I have table A
with 9 records
text, value, dt
1, 1, 2024-01-01
1, 2, 2024-02-01
1, 3, 2024-03-01
1+1, 4, 2024-04-01
1+1, 5, 2024-05-01
2, 6, 2024-05-01
2+1, 7, 2024-06-01
3, 8, 2024-01-01
3, 9, 2024-06-01
I need get 6 records based on the latest date where +1, if no data for +1 then value2 should be null
text, value, dt, value2
1, 1, 2024-01-01, 5
1, 2, 2024-02-01, 5
1, 3, 2024-03-01, 5
2, 6, 2024-05-01, 7
3, 8, 2024-01-01, null
3, 8, 2024-06-01, null
here is one way :
with cte as (
select t1.text
, value
, row_number() over (partition by text order by dt desc) as rn
from my_table t1
where t1.text like '%+1'
)
select t1.* , cte.value as value2
from my_table t1
left join cte
on concat(t1.text, '+1') = cte.text
and cte.rn = 1
where t1.text not like '%+1'
output:
| text | value | dt | value2 |
|---|---|---|---|
| 1 | 1 | 2024-01-01 | 5 |
| 1 | 2 | 2024-02-01 | 5 |
| 1 | 3 | 2024-03-01 | 5 |
| 2 | 6 | 2024-05-01 | 7 |
| 3 | 8 | 2024-01-01 | null |
| 3 | 9 | 2024-06-01 | null |