Using the SQL LAG() function

Question

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

Can you add a larger sample data set which has more missing values? — Tim Biegeleisen
– Tim Biegeleisen, Commented Aug 10, 2023 at 14:11

user9517769 · Accepted Answer · 2023-08-10 15:40:04Z

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

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	1
11	800	98	345	1

fiddle

Collectives™ on Stack Overflow

Using the SQL LAG() function

1 Answer 1

Comments

Your Answer

Hot Network Questions

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	1
11	800	98	345	1

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	1
11	800	98	345	1

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related

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	1
11	800	98	345	1