ANY_VALUE
An aggregate function that arbitrarily picks one value from the group. This can include a NULL value if one is present in the group.
It is a non-deterministic function (returns different results each time they are called with a specific set of input values).
May be used as a window function.
Syntax
Aggregate Function
ANY_VALUE (expression);
Window Function
ANY_VALUE([DISTINCT] (col | expr ) OVER ( [PARTITION BY (col | expr), ...] [ORDER BY (col | expr), ...] ] )
Arguments
expression: any numerical or string expression
Return Type
Returns the same value and type as its argument.
Examples
CREATE TABLE av_employees( emp_id NUMERIC(5), emp_lastname VARCHAR(25), emp_firstname VARCHAR(25), emp_title VARCHAR(25), dept VARCHAR(25), emp_city VARCHAR(25), emp_state VARCHAR(2) ); INSERT INTO av_employees VALUES('014', 'Bateman', 'Patrick','Prod_Mgr', 'prod_dev', 'NYC', 'NY'), ('102', 'Karras', 'Damien', 'Doctor','R&D', 'NYC', 'NY'), ('298', 'Denbrough', 'Bill', 'Salesperson','Sales', 'Bangor', 'ME'), ('399', 'Torrance', 'Jack', 'PR Dir', 'PR','Estes Park','CO'), ('410', 'Wilkes', 'Annie', 'HR Mgr','HR','Silver Creek', 'CO'), ('110', 'Strode', 'Laurie', 'VP Sales','Sales', 'Haddonfield', 'IL'), ('312', 'Cady', 'Max', 'IT Dir', 'IT', 'New Essex','FL'), ('089', 'Whateley', 'Wilbur', 'CEO', 'Sen_Mgmt', 'Dunwich', 'MA'), ('075', 'White', 'Carrie', 'Receptionist', 'HR','Chamberlain', 'ME'), ('263', 'MacNeil', 'Regan', 'R&D Mgr','R&D', 'Washington', 'DC'); SELECT * FROM av_employees; **** +--------+--------------+---------------+--------------+----------+--------------+-----------+ | emp_id | emp_lastname | emp_firstname | emp_title | dept | emp_city | emp_state | +--------+--------------+---------------+--------------+----------+--------------+-----------+ | 102 | Karras | Damien | Doctor | R&D | NYC | NY | | 410 | Wilkes | Annie | HR Mgr | HR | Silver Creek | CO | | 14 | Bateman | Patrick | Prod_Mgr | prod_dev | NYC | NY | | 110 | Strode | Laurie | VP Sales | Sales | Haddonfield | IL | | 298 | Denbrough | Bill | Salesperson | Sales | Bangor | ME | | 89 | Whateley | Wilbur | CEO | Sen_Mgmt | Dunwich | MA | | 75 | White | Carrie | Receptionist | HR | Chamberlain | ME | | 399 | Torrance | Jack | PR Dir | PR | Estes Park | CO | | 312 | Cady | Max | IT Dir | IT | New Essex | FL | | 263 | MacNeil | Regan | R&D Mgr | R&D | Washington | DC | +--------+--------------+---------------+--------------+----------+--------------+-----------+
The following example displays the use of ANY_VALUE
aggregate function with the GROUP BY
statement.
Aggregate Function
SELECT ANY_VALUE(emp_lastname), emp_city FROM av_employees GROUP BY emp_city; **** +-------------------------+--------------+ | ANY_VALUE(emp_lastname) | emp_city | +-------------------------+--------------+ | Strode | Haddonfield | | MacNeil | Washington | | White | Chamberlain | | Torrance | Estes Park | | Denbrough | Bangor | | Bateman | NYC | | Cady | New Essex | | Wilkes | Silver Creek | | Whateley | Dunwich | +-------------------------+--------------+
Window Function
SELECT emp_id, any_value(emp_id) OVER (ORDER BY emp_id) FROM av_employees; **** +--------+------------------------------------------+ | emp_id | any_value(emp_id) OVER (ORDER BY emp_id) | +--------+------------------------------------------+ | 14 | 14 | | 75 | 14 | | 89 | 14 | | 102 | 14 | | 110 | 14 | | 263 | 14 | | 298 | 14 | | 312 | 14 | | 399 | 14 | | 410 | 14 | +--------+------------------------------------------+