I have a simple query that pulls data from multiple tables with a few conditions
select x, step, code, ....,
from
table 1
inner join table 2
inner join table 3
where
'filters'
order by x, codeThis gives me a table where two columns with step and code:
| x | step | code |
|---|---|---|
| 1 | 0 | 0001 |
| 1 | 10 | 0000 |
| 1 | 20 | 0000 |
| 1 | 30 | 0000 |
| 1 | 40 | 0000 |
| 1 | 1000 | 0001 |
| 2 | 0 | 0000 |
| 2 | 10 | 0000 |
| 2 | 20 | 0000 |
As above, the 0 step indicates whether the process failed and the sub-step shows exactly which part. Then when step goes back to 0 it is now a new process.
Is there a way to pull the 0 step value of the code (so in this case it would be "0001") and then use that value as the code until it reaches the next step 0 and update the value?
So the table would now look like this:
| x | step | code |
| ------------ | ------------ | ------------ |
| 1 | 0 | 0001 |
| 1 | 10 | 0001 |
| 1 | 20 | 0001 |
| 1 | 30 | 0001 |
| 1 | 40 | 0001 |
| 1 | 1000 | 0001 |
| 2 | 0 | 0000 |
| 2 | 10 | 0000 |
| 2 | 20 | 0000 |
It can be a new column, I just indicated the same one to save time.
Any help is appreciated, thanks
1条答案
按热度按时间vltsax251#
You confused code with step in your
ORDER BYclause. Change this, so as to get a deterministic result with step 0 always being the first step shown for an x.Use
FIRST_VALUEto get the code for step 0 per x.