ANY_
Warning
SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.
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