# how can i write the max value of different consecutive values in the new column by length

Posted on

### Question :

i have a data frame as “a”.
a:
[,1] 1 2 3 0 1 2 0 [,2] 0 0 1 2 3 0 1 [,3] 1 2 3 4 5 0 0 and dim(a)=7*3

i want to create a new column including maximum value for each set of consecutive numbers till 0. and calculate for next set till next zero. like this:
result:
[,1] 3 3 3 0 2 2 0 [,2] 0 0 3 3 3 0 1 [,3] 5 5 5 5 5 0 0

unfortunatly i couldn’t put the picture of my data frame(a).

If you are doing a difference between first column ([1,]) and d1 you will get something like this:

``````SELECT id,d1
,id-d1 as diff_d1
FROM Table1
``````

output:

``````id  d1  diff_d1
1   1   0
2   2   0
3   3   0
4   0   4
5   1   4
6   2   4
7   0   7
``````

Doing a max on [d1] and grouping by [diff_d1] column you will get : here

``````maxD1
3
2
0
``````

Putting all together

``````;WITH cteSource AS
(SELECT
id,
d1,d2,d3
,MAX(d1) OVER(PARTITION BY id-d1 ORDER BY id
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING)as maxD1
,MAX(d2) OVER(PARTITION BY id-d2 ORDER BY id
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING)as maxD2
,MAX(d3) OVER(PARTITION BY id-d3 ORDER BY id
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING)as maxD3
FROM Table1
)
SELECT id
,CASE WHEN d1<> 0 THEN maxD1
ELSE 0  END AS d1
,CASE WHEN d2 <>  0 THEN maxD2
ELSE 0  END AS d2
,CASE WHEN d3 <>  0 THEN maxD3
ELSE 0  END AS d3
FROM cteSource
``````

Output:

``````id  d1  d2  d3
1   3   0   5
2   3   0   5
3   3   3   5
4   0   3   5
5   2   3   5
6   2   0   0
7   0   1   0
``````

dbfiddle