Say I have the following table:
| ID | T | R |
|---|---|---|
| 1 | 2 | |
| 1 | 3 | Y |
| 1 | 4 | |
| 1 | 5 | |
| 1 | 6 | Y |
| 1 | 7 |
I would like to add a column which equals the value from column T based on the last non-null value from column R. This means the following:
| ID | T | R | GOAL |
|---|---|---|---|
| 1 | 2 | ||
| 1 | 3 | Y | |
| 1 | 4 | Y | 3 |
| 1 | 5 | 4 | |
| 1 | 6 | Y | 4 |
| 1 | 7 | 6 |
I do have many ID's so I need to make use of the OVER (PARTITION BY ...) clause. Also, if possible, I would like to use a single statement, like
SELECT *
, GOAL
FROM TABLE
So without any extra select statement.
CodePudding user response：
T is in ascending order so just null it out according to R and take the maximum looking backward.
select *,
max(case when R is not null then T end)
over (
partition by id
order by T
rows between unbounded preceding and 1 preceding
) as GOAL
from TBL