Oracle Aggregate Function - Oracle/PLSQL MAX Function

This Oracle tutorial explains how to use the Oracle/PLSQL MAX function.

The Oracle/PLSQL MAX function returns the maximum value of an expression.

MAX function operates on a group of rows and return one row of output.

You can use the MAX function with any valid expression. For example, you can use the MAX() with numbers, strings, and datetimes.

Null values are ignored by MAX. You can use the DISTINCT keyword to exclude duplicate entries.

Syntax

The syntax for the Oracle/PLSQL MAX function is:

SELECT MAX( expression )
 FROM tables
 WHERE conditions;

expression can be a numeric field or formula.

The following SQL returns how the maximum salary of all employees.

SELECT MAX(salary) AS "Highest Salary"
 FROM employees;

The following example uses the MAX function to return the name of the department and the maximum salary in the department.

SELECT department, MAX(salary) AS "Highest salary"
 FROM employees
 GROUP BY department;

Example


CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
                  ENAME VARCHAR2(10),
                  JOB VARCHAR2(9),
                  SAL NUMBER(7, 2),
                  DEPTNO NUMBER(2));
-- from   w  w  w.  j a  v a 2s .com
INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK',     800,    20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', 1600,    30);
INSERT INTO EMP VALUES (3, 'WARD',  'SALESMAN', 1250,    30);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER',  2975,    20);
INSERT INTO EMP VALUES (5, 'MARTIN','SALESMAN', 1250,    30);
INSERT INTO EMP VALUES (6, 'BLAKE', 'MANAGER',  2850,    30);
INSERT INTO EMP VALUES (7, 'CLARK', 'MANAGER',  2850,    10);
INSERT INTO EMP VALUES (8, 'SCOTT', 'ANALYST',  3000,    20);
INSERT INTO EMP VALUES (9, 'KING',  'PRESIDENT',3000,    10);
INSERT INTO EMP VALUES (10,'TURNER','SALESMAN', 1500,    30);
INSERT INTO EMP VALUES (11,'ADAMS', 'CLERK',    1500,    20);


SQL>
SQL> select max(empno) from emp;

MAX(EMPNO)
----------
        11

SQL>

String value

When MAX() is working on strings, the strings are ordered alphabetically with the maximum string being at the bottom of a list.


SQL>
SQL> select max(ename) from emp;

MAX(ENAME)
----------
WARD

SQL>

Using the MAX function with the group by clause:


SQL>--  w w w  .ja v  a2  s .c  o  m
SQL> select max(sal) from emp group by deptno;

  MAX(SAL)
----------
      2850
      3000
      3000

SQL>

Date value

Using MAX with date value:


SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
  2                        ENAME VARCHAR2(10),
  3                        HIREDATE DATE);
--  w w w .j  av a2 s . c  om
Table created.

SQL>
SQL> INSERT INTO EMP VALUES (1, 'SMITH', TO_DATE('17-DEC-1980', 'DD-MON-YYYY'));


1 row created.

SQL> INSERT INTO EMP VALUES (2, 'ALLEN', TO_DATE('20-FEB-1981', 'DD-MON-YYYY'));


1 row created.

SQL> INSERT INTO EMP VALUES (3, 'WARD',  TO_DATE('22-FEB-1981', 'DD-MON-YYYY'));


1 row created.

SQL> INSERT INTO EMP VALUES (4, 'JONES', TO_DATE('2-APR-1981',  'DD-MON-YYYY'));


1 row created.

SQL> INSERT INTO EMP VALUES (5, 'MARTIN',TO_DATE('28-SEP-1981', 'DD-MON-YYYY'));


1 row created.

SQL>
SQL> select max(HIREDATE) from emp;

MAX(HIRED
---------
28-SEP-81

SQL>