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 | +--------+------------------------------------------+