SQL carry forward non-null value within groups

Question

The following thread (SQL QUERY replace NULL value in a row with a value from the previous known value) was very helpful to carry forward non-null values, but I'm can't figure out how to add a grouping column.

For example, I would like to do the following:

Example Data

Here is how I would have liked to code it:

UPDATE t1
SET
    @n = COALESCE(number, @n),
    number = COALESCE(number, @n)
GROUP BY grp

I know this isn't possible, and have seen several solutions that rely on inner joins, but those examples focus on aggregation rather than carrying forward values. For example: SQL Server Update Group by.

My attempt to make this work is to do something like the following:

UPDATE t1
SET
    @n = COALESCE(number, @n),
    number = COALESCE(number, @n)
FROM t1
INNER JOIN (
    -- Lost on what to put in the inner join...
    SELECT grp, COUNT(*) FROM t1 GROUP BY grp
) t2
on t1.grp = t2.grp

Pleases provide sample data and desired results *as text in the question* so it is obvious what *you* mean by "carry forward non-NULL values". — Gordon Linoff, Nov 26 '19 at 16:15

score 0 · Accepted Answer · answered Nov 26 '19 at 16:17

I think you can do what you want with a correlated subquery:

UPDATE t1
    SET number = (SELECT TOP (1) tt1.number
                  FROM t tt1
                  WHERE tt1.grp = t1.grp AND tt1.? <= t1.? AND tt1.number IS NOT NULL
                  ORDER BY t1.? DESC
                 )
    FROM t1
    WHERE t1.number IS NULL;

The ? is for the column that specifies "forward" in your expression "carry forward".

SQL carry forward non-null value within groups

1 Answers1