SQL Server Get first non null value in a partition

qyswt5oh 于 2023-11-16 发布在其他

关注(0)|答案(1)|浏览(110)

As SQL Server does not support ignore nulls in first_value function, how can we get the first non null value in a window without defining a new column?

I tried to use COALESCE() , however it didn't work as a window function.

sql-server

来源：https://stackoverflow.com/questions/77433272/get-first-non-null-value-in-a-partition

1条答案

按热度按时间

lx0bsm1f1#

As has been mentioned, FIRST_VALUE does support IGNORE NULLS (in SQL Server 2022+). As such you can do:

FIRST_VALUE(YourColumn) IGNORE NULLS OVER (PARTITION BY SomeColumn ORDER BY AnotherColumn) AS FirstValue

Emulating this for FIRST_VALUE isn't too difficult without the syntax though, just add a CASE to your ORDER BY on your column to order the NULL values later:

FIRST_VALUE(YourColumn) OVER (PARTITION BY SomeColumn ORDER BY CASE WHEN YourColumn IS NULL THEN 1 ELSE 0 END, OtherColumn)

This'll force values that areNULL to be ordered later and so can only be the first value if all the values are NULL .

赞(0）回复(0）举报 2023-11-16

我来回答

SQL Server Get first non null value in a partition

1条答案

相关问题

热门标签

最新问答