Another look at ANY_VALUE in BigQuery

A reminder that ANY_VALUE is a pretty interesting aggregation function in BigQuery SQL.

It gives you a chosen row from a group. Chosen doesn't mean random, but non-deterministic.

Together with HAVING MAX | MIN you can actually control what rows get picked.

While ANY_VALUE works both with GROUP BY and as a window function OVER (PARTITION BY...), the window variety does not yet support HAVING MIN MAX.

Otherwise, when do I use it? A couple of cases, and it's not only for the thrill of getting an item by chance from the group:
- line events also contain header info, so say we need to extract order header data from orderline data
- aggregation after pseudo-pivoting with CASE WHEN value = x, same as we used to do with MIN or MAX before
- other aggregations of string values based on a rule

Found it useful? Subscribe to my Analytics newsletter at notjustsql.com.