4 ms·
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 for
by GolDDranks 3y ago
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.
- ComodoHacker 3y agoYou can do that with standard aggregates by comparing min() and max().
- masklinn 3y agoIn postgres you can array_agg(distinct) and error on the application side if there is more than one value.
- nerdponx 3y agoOr sometimes you can do something like FIRST() OVER (ORDER BY NULL).
- GolDDranks 3y agoI don't think that errors if the group contains different values...?
- nerdponx 3y agoSnowflake has ANY_VALUE() for this: https://docs.snowflake.com/en/sql-reference/functions/any_value https://docs.snowflake.com/en/sql-reference/functions/any_va...