Purpose
This function is used to operate on values in a set of rows, sort these rows according to the specified sort order, and return the value at the first or last position. It can be used as an aggregate or analytic function.
Note
- When used as an analytic function, the
OVERclause must be used to define the window for calculation. This function partitions the query result set into several groups based on one or more expressions inquery_partition_clause. - When used as an aggregate function, this function performs aggregate calculations on a set of rows and returns only one value. In this case, the
OVERclause is not required.
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 | Specifies the type of aggregate function. |
| expr | Can be any expression. |
| FIRST | LAST | Specifies the position of the value to be returned after sorting expr.
|
| DESC | ASC | Specifies the sort order, which is optional.
|
| NULLS { FIRST | LAST } | Specifies the position of NULL values after sorting expr, which is optional.
|
| OVER | Defines the window for calculation using the OVER clause. For more information, see Analytic Function Description. |
Return type
The return type is the same as the data type of the column specified in aggregate_function.
Examples
Create a table named employees 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
Aggregate function example
Sum the salaries of the employees with the highest salary and the lowest salary.
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
Analytic function example
For each department, sum the salaries of the employees with the highest salary and the lowest salary.
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