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

Question

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?

jschildgen · Accepted Answer · 2022-08-16 09:42:55Z

3

Snowflake supports the IGNORE NULLS clause: https://docs.snowflake.com/en/sql-reference/functions/lag.html

So, your query would be:

Select Name, tag as tag_original, coalesce(Tag, lag(Tag) IGNORE NULLS over (order by Name)) as Tag from tags;

Oracle supports IGNORE NULLS as well: https://dbfiddle.uk/?rdbms=oracle_21&fiddle=d108ff84755240ec3b488aed08d80dc5

answered Aug 16, 2022 at 9:42

jschildgen

4,2794 gold badges19 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Lukasz Szozda · Accepted Answer · 2025-09-05 20:46:07Z

INTERPOLATE_BFILL

Updates rows in a time-series data set to gap-fill missing values based on surrounding values.

INTERPOLATE_FFILL: Gap-fills rows based on the previously observed row.

SELECT *, INTERPOLATE_FFILL(Tag) OVER(ORDER BY Name)
FROM tab
ORDER BY Name;

Output:


+------+-----+--------------------------------------------+
| NAME | TAG | INTERPOLATE_FFILL(TAG) OVER(ORDER BY NAME) |
+------+-----+--------------------------------------------+
| a    | 200 |                                        200 |
| b    | 400 |                                        400 |
| c    |     |                                        400 |
| d    |     |                                        400 |
| e    |     |                                        400 |
| f    | 100 |                                        100 |
| g    |     |                                        100 |
| h    |     |                                        100 |
| i    |     |                                        100 |
| j    | 500 |                                        500 |
+------+-----+--------------------------------------------+

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related