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.
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|
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.