SQL select query fill null values based on the least closest non-null value

Viewed 56

I have the following table (suppose the Name column is ascending):

Name    Tag
'a'     200
'b'     400
'c'     null
'd'     null
'e'     null
'f'     100
'g'     null
'h'     null
'i'     null
'j'     500

I want to write a SELECT query in Snowflake to return the null values with the least nearest value, like this:

Name    Tag
'a'     200
'b'     400
'c'     400
'd'     400
'e'     400
'f'     100
'g'     100
'h'     100
'i'     100
'j'     500

If I write the following query, it only fills the first null value for each consequent null group.

Select Name, coalesce(Tag, lag(Tag) over (order by Name)) as Tag

it returns:

Name    Tag
'a'     200
'b'     400
'c'     400
'd'     null
'e'     null
'f'     100
'g'     100
'h'     null
'i'     null
'j'     500

Any idea?

1 Answers
Related