ANY_VALUE syntax, parameters, and return values - MaxCompute

Returns a random value from a specified colname. This is an extended function in MaxCompute 2.0.

Usage notes

MaxCompute V2.0 provides new extended functions. If the functions you use involve new data types, including TINYINT, SMALLINT, INT, FLOAT, VARCHAR, TIMESTAMP, and BINARY, you must execute a statement to enable the MaxCompute V2.0 data type edition:
- Session level: Add SET odps.sql.type.system.odps2=true; before the SQL statement that you want to execute, and commit and execute them together.
- Project level: The project owner can enable the MaxCompute V2.0 data type edition for the project based on the project requirements. Statement:
```
setproject odps.sql.type.system.odps2=true;
```
  The configuration takes effect after 10 to 15 minutes.
  For more information about setproject, see Project operations. For more information about the precautions you must take when enabling the MaxCompute V2.0 data type edition at the project level, see Data type editions.
If you use an SQL statement that includes multiple aggregate functions and the project resources are insufficient, memory overflow may occur. We recommend that you optimize the SQL statement or purchase additional computing resources as needed.

Syntax

ANY_VALUE(<colname>)

Parameters

colname: required. The name of a column, which can be of any data type.

Return value

The data type of the return value is the same as the data type of the colname parameter. If the value of colname is null, the row that contains this value is not used for calculation.

Sample data

This section provides sample source data and examples to help you understand how to use the functions. Create a table named emp and insert the sample data into the table. Sample statement:

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 (path and name) to which you upload the data file

The emp.txt file contains the following sample data:

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: Select one of the employees. Sample statement:

SELECT ANY_VALUE(ename) FROM emp;

The following result is returned:

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

Example 2: Use this function with group by to group all employees based on the deptno column and select one employee from each group. Sample statement:

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

The following result is returned:

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

Related functions

ANY_VALUE is an aggregate function. For more information about the functions that are used to calculate the average value of multiple input records and to aggregate parameters, see Aggregate functions.