Window function for is_unique?

Steffy Alen

3 years ago

I’m looking to see if a value is unique in the column. For example:

; with tbl (value) as (
    select 'hello' UNION ALL
    select 'hello' UNION ALL
    select 'abc' UNION ALL
    select null
) select
    value,
    COUNT(1) OVER (PARTITION BY VALUE) = 1 value_is_unique
from tbl

And the result:

VALUE   VALUE_IS_UNIQUE
hello   FALSE
hello   FALSE
abc     TRUE
        TRUE

Is there a window function that basically does what I’m doing with the COUNT(1) OVER (PARTITION BY VALUE) = 1? Or is the above the suggested way to do this?

https://docs.snowflake.com/en/sql-reference/functions-analytic.html