ANY

Syntax

ANY(expr)

Purpose

This function selects an arbitrary value from a set of values. It is typically used in GROUP BY queries. expr specifies the column from which to select an arbitrary value. By default, this function ignores NULL values.

Examples

1. Enable the ClickHouse extension function mode.

   obclient> ALTER SYSTEM SET sql_func_extension_mode = "ClickHouse";
   

2. Create a test table test_tbl1.

   obclient> CREATE TABLE test_tbl1 (
       col1 INT AUTO_INCREMENT PRIMARY KEY,
       col2 INT NOT NULL,
       col3 DATETIME NOT NULL);
   

3. Insert data into the test_tbl1 table.

   obclient> INSERT INTO test_tbl1(col2, col3) VALUES
       (1, '2026-03-01 10:00:00'),
       (1, '2026-03-01 11:00:00'),
       (2, '2026-03-01 12:00:00'),
       (1, '2026-03-02 10:00:00');
   

4. Use the ANY function to retrieve an arbitrary access date (col3) for each col2.

   obclient> SELECT col2, ANY(col3)
       FROM test_tbl1
       GROUP BY col2;
   

   The result is as follows:

   +------+---------------------+
   | col2 | ANY(col3)           |
   +------+---------------------+
   |    1 | 2026-03-01 10:00:00 |
   |    2 | 2026-03-01 12:00:00 |
   +------+---------------------+
   2 rows in set