CODEWITHSUNDEEP
snowflake-cloud-data-platform
Ward W
Ward W

Reputation: 669

How can I return any NON-NULL value in an aggregate/GROUP BY query?

How can I return any NON-NULL value in an aggregate/GROUP BY SQL query in Snowflake?

Along with other information from a table, I want to get an arbitrary value for a user_id attribute that could be null in the data (but still pull in rows where that attribute is null.) How can I get a value for that attribute that will NOT be one of the null values?

I'd like to do this to avoid the more computationally expensive (I assume) MIN/MAX route for the attribute value.

I'd like to do something like SELECT user_id, ANY_VALUE(attribute) IGNORE NULLS FROM foo but it doesn't seem that IGNORE NULLS is supported like it is for FIRST_VALUE(), for example.

Upvotes: 0

Views: 3632

Answers (1)

Felipe Hoffa
Felipe Hoffa

Reputation: 59175

If your goal is to improve performance, I'd follow Simeon's advice to just do MAX() — it should be fast.

If you want some SQL syntax to just get the first element that is not null array_agg()[0] can get you that — because array_agg() ignores nulls:

with data as ( 
    select 'a' label, $1 i from values (1),(2),(3),(null)
)

select label, array_agg(i)[0]
from data
group by label;

But this will probably be slower and fail if there's too many rows involved.

Upvotes: 3

Related Questions