MAX function
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.
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER(7, 2),
DEPTNO NUMBER(2));
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>
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>
SQL> select max(sal) from emp group by deptno;
MAX(SAL)
----------
2850
3000
3000
SQL>
Using MAX with date value:
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10),
3 HIREDATE DATE);
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>