MAX
Purpose
This function returns the maximum value of expr from the window or group of rows.
Syntax
max::=
Usage Notes
DISTINCT has no effect.
Examples
Aggregate Function
Result
DEPARTMENT | MAX_CURRENT_SALARY |
ACCOUNTS | 80000 |
HR | 90000 |
Analytic Function
SELECT
id, department, hire_date, current_salary,
MAX(current_salary) OVER (PARTITION BY department ORDER BY hire_date) MAX_CURRENT_SALARY
FROM employee_table ORDER BY department, hire_date;
Result
ID | DEPARTMENT | HIRE_DATE | CURRENT_SALARY | MAX_CURRENT_SALARY |
2005 | ACCOUNTS | 2013-01-01 | 80000 | 80000 |
2003 | ACCOUNTS | 2015-07-01 | 80000 | 80000 |
2002 | ACCOUNTS | 2017-01-01 | 65000 | 80000 |
2004 | ACCOUNTS | 2017-01-01 | 70000 | 80000 |
2001 | ACCOUNTS | 2018-07-0 | 55000 | 80000 |
1003 | HR | 2014-01-01 | 90000 | 90000 |
1002 | HR | 2016-01-01 | 70000 | 90000 |
1004 | HR | 2016-01-01 | 70000 | 90000 |
1001 | HR | 2018-07-0 | 55000 | 90000 |