how to use max function in oracle plsql

SQL MAX function returns the highest or maximum value out of the available records/values. Oracle MAX Function is opposite of SQL MIN Function.

---

SQL MAX Function Syntax

SELECT MAX(expression)
FROM table_name
WHERE conditions;

---

SQL MAX Function Examples

Let’s take an example for understanding the SQL MAX Function:

Suppose we have a table named “Employee” with the data as shown below.

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10

Now we will see usage of SQL MAX Function in different scenarios

---

SQL MAX Function – SQL SELECT MAX Usage

The simplest way of using the SQL MAX Function is to fetch the maximum value from a column of table

For example, with the below Oracle MAX Function query will fetch maximum value of commission.

SELECT MAX(commission) as "MAX Commission"
FROM employee;

The above SQL SELECT MAX query will return result as “20” as that’s the highest commission available in the “Employee” table.

Note: We have aliased the MAX(commission) field as “MAX Commission”, hence once the query is run “MAX Commission” is displayed as field name of result from the Oracle MAX Function query.

NULL Values are not considered by the SQL MAX Function.

SQL MAX Function – Using SQL WHERE Clause Example

SQL MAX Function can be used in SQL SELECT Statement having WHERE Clause.

For example, below SQL SELECT MAX query will return maximum salary in ‘Sales’ department.

SELECT MAX(salary) as "Maximum Salary"
FROM employee
WHERE department  = 'Sales';

The above Oracle MAX Function query returns “32000” as maximum salary for ‘Sales’ department.
Also note that we have aliased the MAX(salary) field as “Maximum Salary”.

SQL MAX Function – Using SQL GROUP BY Clause Example

SQL MAX Function can be used in SQL SELECT Statement having SQL GROUP BY Clause.

For example, the Oracle MAX Function query below will return department name and the maximum salary being given in each department.

SELECT department as "Department Name"
       ,MAX(salary) "Maximum Salary"
FROM employee
GROUP BY department;

The data returned by the above SQL SELECT MAX query will be:

DEPARTMENT NAME	MAXIMUM SALARY
Sales	32000
IT	28000
Support	30000

Here notice that by using the SQL SELECT MAX query with SQL GROUP BY Clause we have retrieved the maximum salaries of every unique department along with the department name.

---