# 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

![](https://miro.medium.com/v2/resize:fit:1400/0*jjOYS-gpBpRoyUSf align="left")

*Found it useful? Subscribe to my Analytics newsletter at* [*notjustsql.com*](https://notjustsql.com)*.*