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

Last modified: February 23, 2023

Was this article helpful?