Purpose
KEEP() manipulates values from a set of rows that rank based on a specified sorting rule and returns the first or last value. You can use this function as an aggregate or analytic function.
Note
- When you use this function as an analytic function, use the
OVERclause to define a window over the data on which the function operates. This function divides the query result set into groups based on one or more expressions inquery_partition_clause. - When you use this function as an aggregate function, the function operates on a set of rows and returns a single value. You do not need to add the
OVERclause.
Syntax
aggregate_function KEEP ( DENSE_RANK { FIRST | LAST }
ORDER BY expr [ DESC | ASC ] [ NULLS { FIRST | LAST } ]
[, expr [ DESC | ASC ] [ NULLS { FIRST | LAST } ]]...
)
[ OVER ( [query_partition_clause] ) ]
aggregate_function:
MIN
|MAX
|SUM
|AVG
|COUNT
|VARIANCE
|STDDEV
Parameters
Parameter |
Description |
|---|---|
| aggregate_function | The type of the aggregate function. |
| expr | An expression of any type. |
| FIRST | LAST | The position of the value to be returned after sorting by expr.
|
| DESC | ASC | Optional. The sorting method of the list.
|
| NULLS { FIRST | LAST } | Optional. The position of NULL in expr after sorting.
|
| OVER | You can use the OVER clause to define a window over the data on which the function operates. For more information, see Analytic functions. |
Return type
This function returns a value of the same data type as the column specified in aggregate_function.
Examples
Create the employees table and insert data into it.
obclient> CREATE TABLE employees (department_id INT,manager_id INT,last_name VARCHAR(50),hiredate VARCHAR(50),salary INT);
Query OK, 0 rows affected
obclient> INSERT INTO employees VALUES
(30, 100, 'Raphaely', '2017-07-01', 1700),
(30, 100, 'De Haan', '2018-05-01',11000),
(40, 100, 'Errazuriz', '2017-07-21', 1400),
(50, 100, 'Hartstein', '2019-10-05',14000),
(50, 100, 'Raphaely', '2017-07-22', 1700),
(50, 100, 'Weiss', '2019-10-05',13500),
(90, 100, 'Russell', '2019-07-11', 13000),
(90,100, 'Partners', '2018-12-01',14000);
Query OK, 8 rows affected
Records: 8 Duplicates: 0 Warnings: 0
obclient> SELECT * FROM employees;
+---------------+------------+-----------+------------+--------+
| DEPARTMENT_ID | MANAGER_ID | LAST_NAME | HIREDATE | SALARY |
+---------------+------------+-----------+------------+--------+
| 30 | 100 | Raphaely | 2017-07-01 | 1700 |
| 30 | 100 | De Haan | 2018-05-01 | 11000 |
| 40 | 100 | Errazuriz | 2017-07-21 | 1400 |
| 50 | 100 | Hartstein | 2019-10-05 | 14000 |
| 50 | 100 | Raphaely | 2017-07-22 | 1700 |
| 50 | 100 | Weiss | 2019-10-05 | 13500 |
| 90 | 100 | Russell | 2019-07-11 | 13000 |
| 90 | 100 | Partners | 2018-12-01 | 14000 |
+---------------+------------+-----------+------------+--------+
8 rows in set
Example of an aggregate function
Sort the employees by their salaries and calculate the sum of the salary of the first employee, and then sort the employees by the hire date and calculate the sum of the salary of the last employee.
obclient> SELECT SUM(salary) KEEP (DENSE_RANK FIRST ORDER BY salary) "Worst",
SUM(salary) KEEP (DENSE_RANK LAST ORDER BY HIREDATE) "Best"
FROM employees;
+-------+-------+
| Worst | Best |
+-------+-------+
| 1400 | 27500 |
+-------+-------+
1 row in set
Example of an analytic function
For each department, sort the employees by their salaries and calculate the sum of the salary of the first employee, and then sort the employees by the hire date and calculate the sum of the salary of the last employee.
obclient> SELECT last_name,department_id,salary,
SUM(salary) KEEP (DENSE_RANK FIRST ORDER BY salary)
OVER (PARTITION BY department_id) "Worst",
SUM(salary) KEEP (DENSE_RANK LAST ORDER BY hiredate)
OVER (PARTITION BY department_id) "Best"
FROM employees
ORDER BY department_id, salary, last_name;
+-----------+---------------+--------+-------+-------+
| LAST_NAME | DEPARTMENT_ID | SALARY | Worst | Best |
+-----------+---------------+--------+-------+-------+
| Raphaely | 30 | 1700 | 1700 | 11000 |
| De Haan | 30 | 11000 | 1700 | 11000 |
| Errazuriz | 40 | 1400 | 1400 | 1400 |
| Raphaely | 50 | 1700 | 1700 | 27500 |
| Weiss | 50 | 13500 | 1700 | 27500 |
| Hartstein | 50 | 14000 | 1700 | 27500 |
| Russell | 90 | 13000 | 13000 | 13000 |
| Partners | 90 | 14000 | 13000 | 13000 |
+-----------+---------------+--------+-------+-------+
8 rows in set