Purpose
This function is used to operate on values in a set of rows, sort the rows according to specified rules, and return the value that is either the first or last in the sorted set. It can be used as an aggregate or analytic function.
Note
- When used as an analytic function, you must use the
OVERclause to define the window for computation. This function partitions the query result set into groups based on one or more expressions specified 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 | Any expression. |
| FIRST | LAST | Specifies the position of the value to return after sorting expr.
|
| DESC | ASC | Specifies the sorting order. This is an optional parameter.
|
| NULLS { FIRST | LAST } | Specifies the position of NULL values in the sorted list. This is an optional parameter.
|
| OVER | Use the OVER clause to define the window for computation. 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
Sort employees by salary and sum the salary of the employee with the highest salary; sort employees by hire date and sum the salary of the employee with the latest hire date.
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, sort employees by salary and sum the salary of the employee with the highest salary; sort employees by hire date and sum the salary of the employee with the latest hire date.
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