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() 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  |
+------+-------+------------------+

Now, MySQL returned the rows we expected. This is exactly the same result as not using ANY_VALUE() and disabling the ONLY_FULL_GROUP_BY mode.