ANY_VALUE

ANY_VALUE(arg)

This function is useful for GROUP BY queries when the
ONLY_FULL_GROUP_BY SQL mode is enabled, for cases when MySQL rejects a
query that you know is valid for reasons that MySQL cannot determine.
The function return value and type are the same as the return value and
type of its argument, but the function result is not checked for the
ONLY_FULL_GROUP_BY SQL mode.

For example, if name is a nonindexed column, the following query fails
with ONLY_FULL_GROUP_BY enabled:

mysql> SELECT name, address, MAX(age) FROM t GROUP BY name;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP
BY clause and contains nonaggregated column 'mydb.t.address' which
is not functionally dependent on columns in GROUP BY clause; this
is incompatible with sql_mode=only_full_group_by

The failure occurs because address is a nonaggregated column that is
neither named among GROUP BY columns nor functionally dependent on
them. As a result, the address value for rows within each name group is
nondeterministic. There are multiple ways to cause MySQL to accept the
query:

o Alter the table to make name a primary key or a unique NOT NULL
column. This enables MySQL to determine that address is functionally
dependent on name; that is, address is uniquely determined by name.
(This technique is inapplicable if NULL must be permitted as a valid
name value.)

o Use ANY_VALUE() to refer to address:

SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;

In this case, MySQL ignores the nondeterminism of address values
within each name group and accepts the query. This may be useful if
you simply do not care which value of a nonaggregated column is
chosen for each group. ANY_VALUE() is not an aggregate function,
unlike functions such as SUM() or COUNT(). It simply acts to suppress
the test for nondeterminism.

o Disable ONLY_FULL_GROUP_BY. This is equivalent to using ANY_VALUE()
with ONLY_FULL_GROUP_BY enabled, as described in the previous item.

ANY_VALUE() is also useful if functional dependence exists between
columns but MySQL cannot determine it. The following query is valid
because age is functionally dependent on the grouping column age-1, but
MySQL cannot tell that and rejects the query with ONLY_FULL_GROUP_BY
enabled:

SELECT age FROM t GROUP BY age-1;

To cause MySQL to accept the query, use ANY_VALUE():

SELECT ANY_VALUE(age) FROM t GROUP BY age-1;

ANY_VALUE() can be used for queries that refer to aggregate functions
in the absence of a GROUP BY clause:

mysql> SELECT name, MAX(age) FROM t;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression
#1 of SELECT list contains nonaggregated column 'mydb.t.name'; this
is incompatible with sql_mode=only_full_group_by

Without GROUP BY, there is a single group and it is nondeterministic
which name value to choose for the group. ANY_VALUE() tells MySQL to
accept the query:

SELECT ANY_VALUE(name), MAX(age) FROM t;

It may be that, due to some property of a given data set, you know that
a selected nonaggregated column is effectively functionally dependent
on a GROUP BY column. For example, an application may enforce
uniqueness of one column with respect to another. In this case, using
ANY_VALUE() for the effectively functionally dependent column may make
sense.

For additional discussion, see
https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html.

URL: https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html