Using the SQL LAG() function

I'm trying to use the LAG function in SQL to attempt this but I'm unsure if I am using this incorrectly or not.

What I am trying to do is where you see a NULL under the LOCAL_ID column, fill that NULL with the LOCAL_ID above it.

So, each time you see a 1 under the column ORDER_IN_LOCAL_ID and the start of a new ID under the GROUP_ID column, then fill LOCAL_ID with what ever is in the LOCAL_ID above the NULL.

I have altered the original image to show a larger dataset. The issue I think I have with LAG function is that it it will only take the value directly above it, so my issue is when there are many NULLS, it will just end of taking the NULL above, rather than the LOCAL_ID at the top of that group.

Output :

UNIQUE_ID LOCAL_ID GROUP_ID LOCAL_STAY_ID ORDER_IN_LOCAL_ID
1         808      1        808           1
2         NULL     1        909           2
3         676      7        676           1
4         674      8        674           1
5         352      9        352           1
6         NULL     9        134           2
7         232      11       232           1
8         NULL     11       431           2
9         NULL     11       323           3
10        NULL     11       567           4
11        800      98       800           1
11        NULL     98       786           2
11        NULL     98       345           3

Output :

UNIQUE_ID LOCAL_ID GROUP_ID LOCAL_STAY_ID ORDER_IN_LOCAL_ID
1         808      1        808           1
2         808      1        909           2
3         676      7        676           1
4         674      8        674           1
5         352      9        352           1
6         352      9        134           2
7         232      11       232           1
8         232      11       431           2
9         232      11       323           3
10        232      11       567           4
11        800      98       800           1
11        800      98       786           2
11        800      98       345           3

Query :

,CASE    WHEN a.LOCAL_ID IS NULL  THEN LAG(a.LOCAL_ID) OVER (
        ORDER BY b.GROUP_ID
        ) ELSE a.LOCAL_ID END  AS ANSWER

CREATE TABLE your_table (
  UNIQUE_ID INT,
  LOCAL_ID INT,
  GROUP_ID INT,
  LOCAL_STAY_ID INT,
  ORDER_IN_LOCAL_ID INT
);

INSERT INTO your_table (UNIQUE_ID, LOCAL_ID, GROUP_ID, LOCAL_STAY_ID, ORDER_IN_LOCAL_ID) VALUES
  (1,  808, 1,  808, 1),
  (2, NULL, 1,  909, 2),
  (3,  676, 7,  676, 1),
  (4,  674, 8,  674, 1),
  (5,  352, 9,  352, 1),
  (6, NULL, 9,  134, 2),
  (7,  232, 11, 232, 1),
  (8, NULL, 11, 431, 2),
  (9, NULL, 11, 323, 3),
  (10, NULL, 11, 567, 4),
  (11, 800, 98, 800, 1),
  (11, NULL, 98, 786, 1),
  (11, NULL, 98, 345, 1);

Records: 13  Duplicates: 0  Warnings: 0

CREATE TEMPORARY TABLE temp_table AS
WITH RECURSIVE fill_nulls AS (
  SELECT 
    UNIQUE_ID,
    CASE WHEN ORDER_IN_LOCAL_ID = 1 THEN LOCAL_ID ELSE NULL END AS LOCAL_ID
  FROM your_table
  WHERE ORDER_IN_LOCAL_ID = 1

  UNION ALL

  SELECT
    t.UNIQUE_ID,
    CASE WHEN t.ORDER_IN_LOCAL_ID = 1 THEN t.LOCAL_ID ELSE fn.LOCAL_ID END
  FROM your_table t
  JOIN fill_nulls fn ON t.UNIQUE_ID = fn.UNIQUE_ID + 1
)
SELECT * FROM fill_nulls;

Records: 53  Duplicates: 0  Warnings: 0

UPDATE your_table AS orig
JOIN temp_table AS temp
ON orig.UNIQUE_ID = temp.UNIQUE_ID
SET orig.LOCAL_ID = temp.LOCAL_ID
WHERE orig.LOCAL_ID IS NULL;

Rows matched: 7  Changed: 7  Warnings: 0

SELECT * FROM your_table

fiddle