如何从列中选取单个值并使用PARTITION BY在另一列中复制该值？

The problem with your current query is that the case when Height is not null condition limits the NewHeight value to only rows where Height isn't null. For rows where Height is null, NewHeight stays null—this doesn’t match your desired output where every row in the same Name group gets the non-null Height value.

To fix this, simply remove the case statement. The MAX(Height) OVER(PARTITION BY Name) expression automatically ignores null values and returns the non-null Height for all rows in the same Name group, even when the current row’s Height is null.

Corrected Query: #

SELECT Name, Height, MAX(Height) OVER(PARTITION BY Name) AS NewHeight 
FROM MyTable;

Expected Output: #

Why This Works: #

The MAX() aggregate function skips NULL values, so when grouped by Name, it picks up the only non-null Height value for each group. The OVER(PARTITION BY Name) clause ensures this value is applied to every row in the group, regardless of whether the individual row’s Height is null or not.

内容的提问来源于stack exchange，提问作者luanstat