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