Purpose
You can call this function to manipulate values from a set of rows that rank based on a specified sorting rule and return the first or last value. You can use it as an aggregate or analytic function.
Note
- When you use it 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 it 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 | Specifies the type of the aggregate function. |
| expr | An expression of any type. |
| FIRST LAST | Specifies 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 about analytic functions, see Analytic functions. |
Return types
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
Aggregate example
Assume that you want to sort the employees by their salaries and calculate the sum of the salary of the first employee. Then, you want to 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
Analytic example
Assume that, for each department, you want to sort the employees by their salaries and calculate the sum of the salary of the first employee. Then, you want to 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