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

Copy 
SELECT 
    department, 
    MAX(current_salary) MAX_CURRENT_SALARY
FROM employee_table GROUP BY department;
Result
DEPARTMENT MAX_CURRENT_SALARY
ACCOUNTS 80000
HR 90000

Analytic Function

Copy 
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