MaxCompute:ANY_VALUE

Syntax

ANY_VALUE(<colname>)

Parameters

Return value

Same data type as colname.

Usage notes

• ANY_VALUE is a MaxCompute V2.0 extended function. If colname uses any of the following data types, enable the MaxCompute V2.0 data type edition before running the query: TINYINT, SMALLINT, INT, FLOAT, VARCHAR, TIMESTAMP, BINARY. Session level — Add the following statement before your SQL statement and run both together:

  SET odps.sql.type.system.odps2=true;

  Project level — Run the following statement as the project owner:

  setproject odps.sql.type.system.odps2=true;

  The setting takes effect within 10–15 minutes. For details, see Project operations and Data type editions.

• If a query contains multiple aggregate functions and your project has limited compute resources, memory overflow may occur. Optimize the SQL statement or purchase additional compute resources as needed.

Sample data

The examples in the next section use the following table and data. To recreate it, run:

CREATE TABLE IF NOT EXISTS emp
   (empno   BIGINT,
    ename   STRING,
    job     STRING,
    mgr     BIGINT,
    hiredate DATETIME,
    sal     BIGINT,
    comm    BIGINT,
    deptno  BIGINT);

tunnel upload emp.txt emp;  -- Replace emp.txt with the actual path to your data file

emp.txt contents:

7369,SMITH,CLERK,7902,1980-12-17 00:00:00,800,,20
7499,ALLEN,SALESMAN,7698,1981-02-20 00:00:00,1600,300,30
7521,WARD,SALESMAN,7698,1981-02-22 00:00:00,1250,500,30
7566,JONES,MANAGER,7839,1981-04-02 00:00:00,2975,,20
7654,MARTIN,SALESMAN,7698,1981-09-28 00:00:00,1250,1400,30
7698,BLAKE,MANAGER,7839,1981-05-01 00:00:00,2850,,30
7782,CLARK,MANAGER,7839,1981-06-09 00:00:00,2450,,10
7788,SCOTT,ANALYST,7566,1987-04-19 00:00:00,3000,,20
7839,KING,PRESIDENT,,1981-11-17 00:00:00,5000,,10
7844,TURNER,SALESMAN,7698,1981-09-08 00:00:00,1500,0,30
7876,ADAMS,CLERK,7788,1987-05-23 00:00:00,1100,,20
7900,JAMES,CLERK,7698,1981-12-03 00:00:00,950,,30
7902,FORD,ANALYST,7566,1981-12-03 00:00:00,3000,,20
7934,MILLER,CLERK,7782,1982-01-23 00:00:00,1300,,10
7948,JACCKA,CLERK,7782,1981-04-12 00:00:00,5000,,10
7956,WELAN,CLERK,7649,1982-07-20 00:00:00,2450,,10
7956,TEBAGE,CLERK,7748,1982-12-30 00:00:00,1300,,10

Examples

Example 1: Return one employee name from the entire table

SELECT ANY_VALUE(ename) FROM emp;

Result:

+------------+
| _c0        |
+------------+
| SMITH      |
+------------+

Example 2: Return one employee name per department using `GROUP BY`

SELECT deptno, ANY_VALUE(ename) FROM emp GROUP BY deptno;

Result:

+------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | CLARK      |
| 20         | SMITH      |
| 30         | ALLEN      |
+------------+------------+

What's next

ANY_VALUE is an aggregate function. For other aggregate functions such as COUNT, SUM, and AVG, see Aggregate functions.