MySQL ANY_VALUE() Function

In MySQL, the ANY_VALUE() function returns any value in the specified column. It is used in a GROUP BY statement to suppress errors caused by ONLY_FULL_GROUP_BY.

ANY_VALUE() is not an aggregate function. It suppresses MySQL’s check that a value is determined by the group; if a group contains different values, the selected value is not deterministic. It also does not select the value from the row that produced another aggregate such as MAX(salary). For solutions to Error 1055, see MySQL Error 1055: ONLY_FULL_GROUP_BY.

ANY_VALUE() Syntax

Here is the syntax of the MySQL ANY_VALUE() function:

ANY_VALUE(column_name)

Parameters

column_name

Required. A column name.

Return value

The MySQL ANY_VALUE() function returns any value in the specified column.

ANY_VALUE() Examples

To demonstrate the usage of the ANY_VALUE() function, let’s create a table test_any_value with three columns id, user, and address, using the following statement:

CREATE TABLE test_any_value (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    address VARCHAR(255) NOT NULL
);

Then, let’s insert 3 rows into the table:

INSERT INTO test_any_value (name, address)
VALUES
    ('Tom', 'Address 1 of Tom'),
    ('Tom', 'Address 2 of Tom'),
    ('Adam', 'Address of Adam');

Let’s look at the rows in the table:

SELECT * FROM test_any_value;

+----+------+------------------+
| id | name | address          |
+----+------+------------------+
|  1 | Tom  | Address 1 of Tom |
|  2 | Tom  | Address 2 of Tom |
|  3 | Adam | Address of Adam  |
+----+------+------------------+

Now, we want to count the number of addresses for each name and display any address for each name:

SELECT name,
    COUNT(address) AS count,
    address
FROM test_any_value
GROUP BY name;

When we execute this statement, MySQL returned an error: “ERROR 1055 (42000): Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column ’testdb.test_any_value.address’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by”.

How to solve it? let’s use the ANY_VALUE() function:

SELECT name,
    COUNT(address) AS count,
    ANY_VALUE(address) AS address
FROM test_any_value
GROUP BY name;

+------+-------+------------------+
| name | count | address          |
+------+-------+------------------+
| Tom  |     2 | Address 1 of Tom |
| Adam |     1 | Address of Adam  |
+------+-------+------------------+

MySQL now accepts the query. The address shown for Tom can be either of that name’s addresses; use ANY_VALUE() only when either value is acceptable. This has the same nondeterministic choice as disabling ONLY_FULL_GROUP_BY, without turning off the mode for the whole session.