ANY_VALUE

The ANY_VALUE aggregate function returns an arbitrary value from a set of values in a group.

expression: The argument values can be any built-in data type.

The data type and length attribute of the result are the same as the data type and length attribute of the argument values. When the argument is a string, the result has the same CCSID as the argument. The result can be null.

The function is applied to the set of values derived from the argument values by the elimination of null values.

If the set of values is empty, the result is a null value. Otherwise, the result is one value from the set.

The specification of DISTINCT has no effect on the result and is not advised.

Example

Using the PROJACT and ACT tables, return the count of each type of activity for a project. Include a description of the activity.

     SELECT COUNT(*), PROJNO, A.ACTNO, ANY_VALUE(ACTDESC)
       FROM PROJACT P, ACT A
       WHERE A.ACTNO = P.ACTNO
       GROUP BY PROJNO, A.ACTNO
       ORDER BY PROJNO