Create a 'partial' window function to update data in SQL Server

Question

I have a table where I want to use window functions to fill in some NULLs, but I only want data to flow downwards, based on a Rank column in the table. Using window functions (PARTITION BY), all rows get assigned the same data, which is not the requirement here.

The initial table has NULL values for columns A and B where Rank=2 and ID=1, which I want to populate with the values where Rank=1. Column C is NULL where Rank=1, and 15 where Rank=2 and ID=1, which needs to stay the same way.

Here is the structure of the initial table, the desired output, as well as some sample code. I am unsure how to incorporate the rank into the partition by statement Initial Table

ID    A       B      C      Rank
---------------------------------
1     10      10     NULL   1
1     NULL    NULL   15     2
2     10      NULL   NULL   1
2     NULL    NULL   15     2
2     NULL    NULL   15     3

Target table

ID    A       B      C      Rank
---------------------------------
1     10      10     NULL   1
1     10      10     15     2
2     10      NULL   NULL   1
2     10      NULL   15     2
2     10      NULL   15     3  

SQL query:

SELECT
    ID,
    MAX(A) OVER (PARTITION BY ID),
    MAX(B) OVER (PARTITION BY ID),
    MAX(C) OVER (PARTITION BY ID),
    Rank
FROM 
    TBL;

As expected, partitioning by both, ID and Rank leads to no changes in the initial table

Answer 1

You can use first_value():

select
    id,
    coalesce(a, first_value(a) over (partition by id order by rnk)) a,
    coalesce(b, first_value(b) over (partition by id order by rnk)) b,
    coalesce(c, first_value(c) over (partition by id order by rnk)) c,
    rnk
from tbl;

Note that rank is a language keyword (as in window function rank() over()), hence not a good choice for a column name. I renamed it to rnk in the query.

Demo on DB Fiddle:

id |  a |    b |    c | rnk
-: | -: | ---: | ---: | --:
 1 | 10 |   10 | null |   1
 1 | 10 |   10 |   15 |   2
 2 | 10 | null | null |   1
 2 | 10 | null |   15 |   2
 2 | 10 | null |   15 |   3