Hacker News Reader

Sometimes I'd like to pick a non-aggregated, non-grouped column that I know will be the same for the whole group. (I often write SQL in GCP's BigQuery that forbids indeterminate results in group bys) I'd like to have an aggregate function something like assert_same(), that picks the value, and errors when some of the values in the group are unexpectedly different.

View on HN

You can do that with standard aggregates by comparing min() and max().

In postgres you can array_agg(distinct) and error on the application side if there is more than one value.

Or sometimes you can do something like FIRST() OVER (ORDER BY NULL).

I don't think that errors if the group contains different values...?

Snowflake has ANY_VALUE() for this: https://docs.snowflake.com/en/sql-reference/functions/any_va...

Reply on news.ycombinator.com