SQL Server Lag by partitioned group

Question

I have a table of data as follows:

+----+-------+----------+
| id | value | group_id |
+----+-------+----------+
|  1 |  -200 |        0 |
|  2 |  -620 |        0 |
|  3 |  -310 |        0 |
|  4 |   400 |        1 |
|  5 |   300 |        1 |
|  6 |   100 |        1 |
|  7 |  -200 |        2 |
|  8 |  -400 |        2 |
|  9 |  -500 |        2 |
+----+-------+----------+

What I would like to do is produce a 4th column that, for each record, shows the last value of the preceding group_id.

So the result I want is as follows:

+----+-------+----------+----------------+
| id | value | group_id | LastValByGroup |
+----+-------+----------+----------------+
|  1 |  -200 |        0 |              0 |
|  2 |  -620 |        0 |              0 |
|  3 |  -310 |        0 |              0 |
|  4 |   400 |        1 |           -310 |
|  5 |   300 |        1 |           -310 |
|  6 |   100 |        1 |           -310 |
|  7 |  -200 |        2 |            100 |
|  8 |  -400 |        2 |            100 |
|  9 |  -500 |        2 |            100 |
+----+-------+----------+----------------+

What I have done so far is in 2 parts. First I use the LAST_VALUE function to get the last Value in each group. Then I have tried to use the LAG function to get the last value from the previous group. Unfortunately the second part of my code isn't working as desired.

Here is my code:

CREATE TABLE #temp
(
    id int identity(1,1),
    value int,
    group_id int
)

INSERT #temp VALUES(-200,0)
INSERT #temp VALUES(-620,0)
INSERT #temp VALUES(-310,0)
INSERT #temp VALUES(400,1)
INSERT #temp VALUES(300,1)
INSERT #temp VALUES(100,1)
INSERT #temp VALUES(-200,3)
INSERT #temp VALUES(-400,3)
INSERT #temp VALUES(-500,3)

;WITH cte AS
(
    SELECT
        *,
        LastValByGroup = LAST_VALUE(Value) OVER(Partition By group_id ORDER BY id 
                                 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) 
    FROM
        #temp
), lagged AS
(
    SELECT
        *,
        LaggedLastValByGroup = LAG(LastValByGroup,1,0) OVER(Partition By group_id ORDER BY id)  
    FROM
        cte
)
SELECT * FROM lagged ORDER BY id

DROP TABLE #temp

And this is the result I get:

+----+-------+----------+----------------+----------------------+
| id | value | group_id | LastValByGroup | LaggedLastValByGroup |
+----+-------+----------+----------------+----------------------+
|  1 |  -200 |        0 |           -310 |                    0 |
|  2 |  -620 |        0 |           -310 |                 -310 |
|  3 |  -310 |        0 |           -310 |                 -310 |
|  4 |   400 |        1 |            100 |                    0 |
|  5 |   300 |        1 |            100 |                  100 |
|  6 |   100 |        1 |            100 |                  100 |
|  7 |  -200 |        3 |           -500 |                    0 |
|  8 |  -400 |        3 |           -500 |                 -500 |
|  9 |  -500 |        3 |           -500 |                 -500 |
+----+-------+----------+----------------+----------------------+

Any help is much appreciated.

Thanks

Answer 1

You can use first_value like following to get the desired result.

select distinct t2.*, ISNULL(FIRST_VALUE(t1.[value]) over(partition by t1.group_id order by t1.id desc), 0) LastValByGroup
from @data t1
right join @data t2 on t1.group_id + 1 = t2.group_id

Please find the db<>fiddle here.