Oracle Max Function with Examples

Oracle Max Function with Examples | We will discuss what is the max analytical function in the Oracle database and how to use it in SQL queries. We will also see how to use the max function in the WHERE clause in the Oracle database.

Max Analytical Function in Oracle Database

The max() analytical function in the Oracle database returns the greatest value of the specified column. Both string columns and numeric columns are compatible with the MAX analytical function.

Syntax of the max() analytical function:-
MAX( [ DISTINCT | ALL ] expression) [ over (analytic_clause) ]

The MAX analytic function can be used as below:-

SELECT Max(columnname)
FROM   tablename;

Oracle Max Function Example

Let us see some examples of the max() function of the Oracle database. For the demonstration, we are going to use the EMP & DEPT table. See more here:- EMP DEPT Tables Queries

1. MAX Oracle Function with Numeric Data Type

From the EMP table, determine the max sal (Salary). In order to do this, we must provide the Salary column to the MAX function as seen in the SQL statement below.

SELECT max(sal) FROM emp; 

Output:-

MAX(SAL)
————-
5000

SELECT max(sal) AS Max_Salary
FROM emp; 

Output:-

MAX_SALARY
——————-
5000

The output we get when running the SELECT statement above is as follows. Here, we find the number 5000, which is the highest figure recorded in the Employee table’s Salary field.

2. MAX Function in Oracle with String Values

In Oracle, you can also use the MAX function on string values, as shown in the example below.

SELECT max(ename) AS Max_Name
FROM emp;

Output:-

MAX_NAME
—————-
WARD

In this case, we get the largest value in the name column which is also the largest value alphabetically. The MAX function sorts string column values alphabetically and returns the first descending value from the column.

3. MAX Oracle function with GROUP by Clause

Let’s determine the maximum salary in each department. To do so, we must pass the salary column to the MAX function and group the employees by department using the GROUP by clause, as shown in the query below.

SELECT deptno, max(sal) AS Max_Salary
FROM emp
GROUP BY deptno;

Output:

DEPTNO  MAX_SALARY
------  ----------
    30       2850
    10       5000
    20       3000

4. MAX Function in Oracle with WHERE Clause 

Let us determine the highest salary in a given department. To do so, we must pass the Salary column to the MAX aggregate function and filter the department using the WHERE clause, as shown in the query below. This is how we use the max function in the where clause in oracle.

SELECT max(sal) AS Max_Salary
FROM emp WHERE deptno = 20;

Output:-

MAX_SALARY
——————-
3000

MAX Analytic Function in PARTITION Clause

By including the partitioning clause, we can display the maximum salary per department, as well as employee data for each department.

SELECT empno, ename, deptno, sal, 
max(sal) over (PARTITION BY deptno) AS max_sal_by_dept
FROM emp;

Output:-

EMPNO	ENAME	DEPTNO	SAL	MAX_SAL_BY_DEPT
-----   ------ ------  ----    ---------------
7782	CLARK	10	2450	5000
7934	MILLER	10	1300	5000
7839	KING	10	5000	5000
7902	FORD	20	3000	3000
7788	SCOTT	20	3000	3000
7566	JONES	20	2975	3000
7369	SMITH	20	800	3000
7876	ADAMS	20	1100	3000
7521	WARD	30	1250	2850
7654	MARTIN	30	1250	2850
7844	TURNER	30	1500	2850
7900	JAMES	30	950	2850
7499	ALLEN	30	1600	2850
7698	BLAKE	30	2850	2850

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!

Search