ANY_VALUE function - Amazon Redshift
SyntaxArgumentsReturnsUsage notesExamples
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

ANY_VALUE function

The ANY_VALUE function returns any value from the input expression values nondeterministically. This function returns NULL if the input expression doesn't result in any rows being returned. The function can also return NULL if there are NULL values in the input expression.

Syntax

ANY_VALUE( [ DISTINCT | ALL ] expression )

Arguments

DISTINCT | ALL

Specify either DISTINCT or ALL to return any value from the input expression values. The DISTINCT argument has no effect and is ignored.

expression

The target column or expression on which the function operates. The expression is one of the following data types:

  • SMALLINT

  • INTEGER

  • BIGINT

  • DECIMAL

  • REAL

  • DOUBLE PRECISON

  • BOOLEAN

  • CHAR

  • VARCHAR

  • DATE

  • TIMESTAMP

  • TIMESTAMPTZ

  • TIME

  • TIMETZ

  • INTERVAL YEAR TO MONTH

  • INTERVAL DAY TO SECOND

  • VARBYTE

  • SUPER

  • HLLSKETCH

  • GEOMETRY

  • GEOGRAPHY

Returns

Returns the same data type as expression.

Usage notes

If a statement that specifies the ANY_VALUE function for a column also includes a second column reference, the second column must appear in a GROUP BY clause or be included in an aggregate function.

Examples

The examples use the event table that is created in Step 4: Load sample data from Amazon S3 in the Amazon Redshift Getting Started Guide. The following example returns an instance of any dateid where the eventname is Eagles. 

select any_value(dateid) as dateid, eventname from event where eventname ='Eagles' group by eventname;

Following are the results.

dateid | eventname
-------+---------------
 1878  | Eagles

The following example returns an instance of any dateid where the eventname is Eagles or Cold War Kids. 

select any_value(dateid) as dateid, eventname from event where eventname in('Eagles', 'Cold War Kids') group by eventname;

Following are the results.

dateid | eventname
-------+---------------
 1922  | Cold War Kids
 1878  | Eagles