The SQL:2023 Standard defines a new aggregate named ANY_VALUE. It returns an implementation-dependent (i.e. non-deterministic) value from the rows in its group.
PFA an implementation of this aggregate.
Can we please add "first_value" and "last_value" if we are going to add "some_random_value" to our library of aggregates?
Also, maybe we should have any_value do something like compute a 50/50 chance that any new value seen replaces the existing chosen value, instead of simply returning the first value all the time. Maybe even prohibit the first value from being chosen so long as a second value appears.
David J.
Adding to the pile of wanted aggregates: in the past I've lobbied for only_value() which is like first_value() but it raises an error on encountering a second value.