SQL MAX Function

SQL MAX FunctionSQL 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.


Tagged , , , , , , , , , , , . Bookmark the permalink.