ANY_
On this page
An aggregate function that arbitrarily picks one value from 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_ 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 |
+--------+------------------------------------------+Last modified: September 6, 2024