SQL SELECT MAX
The SQL Max() function is an aggregate function in SQL. This function returns the values which are greater in the condition. The condition may be a number, or it may be a string expression.
The syntax for the select max function:
SELECT MAX(column_name) FROM table_name WHERE conditions;
Let’s take deep dive into the SQL SELECT MAX.
Consider the already existing tables, which have the following data:
Table Name: Employees
| EMPLOYEEID | FIRST_NAME | LAST_NAME | SALARY | CITY | DEPARTMENT | MANAGERID |
| 1001 | VAIBHAVI | MISHRA | 65500 | PUNE | ORACLE | 1 |
| 1002 | VAIBHAV | SHARMA | 60000 | NOIDA | C# | 5 |
| 1003 | NIKHIL | VANI | 50500 | JAIPUR | FMW | 2 |
| 2001 | PRACHI | SHARMA | 55500 | CHANDIGARH | ORACLE | 1 |
| 2002 | BHAVESH | JAIN | 65500 | PUNE | FMW | 2 |
| 2003 | RUCHIKA | JAIN | 50000 | MUMBAI | C# | 5 |
| 3001 | PRANOTI | SHENDE | 55500 | PUNE | JAVA | 3 |
| 3002 | ANUJA | WANRE | 50500 | JAIPUR | FMW | 2 |
| 3003 | DEEPAM | JAUHARI | 58500 | MUMBAI | JAVA | 3 |
| 4001 | RAJESH | GOUD | 60500 | MUMBAI | TESTING | 4 |
| 4002 | ASHWINI | BAGHAT | 54500 | NOIDA | JAVA | 3 |
| 4003 | RUCHIKA | AGARWAL | 60000 | DELHI | ORACLE | 1 |
| 5001 | ARCHIT | SHARMA | 55500 | DELHI | TESTING | 4 |
Example 1: Execute a query that searches the max salary of employees from the employees' table.
SELECT MAX (SALARY) AS 'MAXSALARY' FROM EMPLOYEES;
In the above query, we find the maximum salary from the entire employees' table. We have named the Max (salary) function as max salary. The salary will be displayed as the column name when the output is returned.
Output:
| MAX SALARY |
| 65500 |
As you can see, the maximum salary is 65500 from the employees' table
Example 2: Execute a query to search the max salary of employees whose residing city is Mumbai from the employees' table.
SELECT CITY, MAX(SALARY) AS 'SALARY' FROM EMPLOYEES WHERE CITY = 'MUMBAI';
We have named max (salary) as salary in the above query, displayed as column name when output is returned. We have found the max salary of an employee in Mumbai and displayed the city name.
Output:
| CITY | SALARY |
| MUMBAI | 60500 |
As you can see, the max salary of an employee who resides in the Mumbai city is 60500
Example 3: Write a query to find the employees' maximum salary from the employees' table of employees whose Department is Java.
SELECT DEPARTMENT, MAX(SALARY) AS 'MAXIMUM_SALARY' FROM EMPLOYEES WHERE DEPARTMENT = 'JAVA';
In the above query, we have aliased max (salary) as maximum_salary, displayed as column name when output is returned. We have found the maximum salary of an employee whose Department is Java and displayed the department name.
Output:
| DEPARTMENT | MAXIMUM_SALARY |
| JAVA | 58500 |
As you can see, the maximum salary of an employee from the Java department is 58500
Example 4: Write a query to find the maximum salary of employees whose Department includes one of the lists is Oracle and FMW from the employees' table.
SELECT MAX(SALARY) AS 'SALARY' FROM EMPLOYEES WHERE DEPARTMENT IN ('ORACLE', 'FMW');
We have aliased max (salary) in the above query, displayed as column name when output is returned. We have found the maximum salary of an employee whose Department is Oracle and FMW.
Output:
| SALARY |
| 65500 |
Example 5: Execute a query to search the max salary of employees whose salary is greater than 55000 and city includes Noida, Delhi from employees’ table group by city.
SELECT CITY, MAX(SALARY) AS EMPLOYEE_SALARY FROM EMPLOYEES WHERE SALARY > 55000 AND CITY IN ('NOIDA', 'DELHI') GROUP BY CITY;
We have named Max (salary) as EMPLOYEE_SALARY in the above query, displayed as a column name when output is returned. We have found the maximum salary of an employee whose salary is greater than 55000. Also, the employee city must be one of the names included in the city IN parameter. We also used the GROUP BY clause followed by the city column. The Noida city will be grouped into one city of all the employees whose city in Noida and found the employees' salary, and the same approach is used for Delhi city.
Output:
| CITY | EMPLOYEE_SALARY |
| DELHI | 60000 |
| NOIDA | 60000 |
Employee resides in Delhi city, the max salary is 60000, and Employee resides in Noida city, max salary is 60000.
Example 6: Execute a query to search max employee salary from Employee's table cities are unique of employees group by Department.
SELECT CITY, MAX(DISTINCT SALARY) AS 'EMPLOYEE_SALARY' FROM EMPLOYEES GROUP BY DEPARTMENT;
We have named max (DISTINCT salary) as Employee_salary in the above query, displayed as column name when output is returned. We have found the maximum employee salary from the employees' table considering unique cities in a group by department column.
Output:
| DEPARTMENT | EMPLOYEE_SALARY |
| NOIDA | 60000 |
| JAIPUR | 65500 |
| PUNE | 58500 |
| PUNE | 65500 |
| MUMBAI | 60500 |
Example 7: Write a query to find the maximum employee salary from the Employee's table unique department of employees group by the Department.
SELECT DEPARTMENT, MAX(DISTINCT SALARY) AS 'EMPLOYEE_SALARY' FROM EMPLOYEES GROUP BY DEPARTMENT;
In the above query, we have aliased max (DISTINCT salary) as Employee_salary, displayed as column name when output is returned. We have found the maximum employee salary from the employees' table considering unique departments grouped by department column.
Output:
| DEPARTMENT | EMPLOYEE_SALARY |
| C# | 60000 |
| FMW | 65500 |
| JAVA | 58500 |
| ORACLE | 65500 |
| TESTING | 60500 |
Example 8: Execute a query to search the maximum salary of the Employee from the Employee's table group by the Department where max() salary is greater than 55000.
SELECT CITY, MAX(SALARY) AS SALARY FROM EMPLOYEES GROUP BY DEPARTMENT HAVING MAX(SALARY) > 55000;
We have named max (salary) as salary in the above query, displayed as column name when output is returned. We have found maximum employee salary from the employees' table followed group by department name and used having a condition where max() is greater than 55000.
Output:
| DEPARTMENT | EMPLOYEE_SALARY |
| NOIDA | 60000 |
| JAIPUR | 65500 |
| PUNE | 58500 |
| PUNE | 65500 |
| MUMBAI | 60500 |
As we can see, only cities with aggregate salaries are greater than 55000. Having a clause is like where clause. Having a clause is used when we want to apply some condition to the aggregate function. We used having clause only when we used aggregate function in the query.
Example 9: Write a query to find an employee's maximum salary with a laptop group by the Department.
SELECT DEPARTMENT, MAX(SALARY) AS SALARY FROM EMPLOYEES WHERE EMPLOYEEID IN (SELECT EMPLOYEEID FROM LAPTOP) GROUP BY DEPARTMENT;
We have named max (salary) as salary in the above query, displayed as column name when output is returned. Explanation of the above query, First sub-query will get executed (SELECT EMPLOYEEID FROM LAPTOP); as a result, we will get the employee ids, including the null value. After the sub-query is executed, the main query will get executed SELECT DEPARTMENT, MAX(SALARY) AS SALARY FROM EMPLOYEES WHERE EMPLOYEEID IN (output of sub-query). In the IN operator of the main query, there is an employee's id, which is output from the sub-query according to the IN operator. We will get our final result which is the group by department name.
Output:
| DEPARTMENT | SALARY |
| C# | 60000 |
| JAVA | 58500 |
| ORACLE | 60000 |
| TESTING | 55500 |
