SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL Data Types SQL OPERATORS SQL COMMANDS SQL Queries

SQL Database

SQL Create Database SQL DROP Database SQL SELECT Database

SQL Table

SQL TABLE SQL CREATE TABLE SQL COPY TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL DROP TABLE SQL UPDATE TABLE SQL INSERT TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT WHERE Clause SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT OR Operator SQL SELECT LIKE Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL WHERE Clause SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT Statement SQL INSERT INTO Statement SQL INSERT INTO Values SQL INSERT INTO SELECT SQL Insert multiple rows

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL CROSS Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Cast Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS Difference between Delete, Drop and Truncate in SQL

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL Aggregate Operators SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL SQL Formatter WEB SQL SQL Auto Increment Save Point in SQL space() function in SQL SQL Aggregate Functions SQL Topological Sorting SQL Injection SQL Cloning Tables SQL Aliases SQL Handling Duplicate Update Query in SQL Grant Command in SQL SQL SET Keyword SQL Order BY LIMIT SQL Order BY RANDOM

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL How index works in SQL How to calculate age from Date of Birth in SQL How to Rename Column name in SQL What are single row and multiple row subqueries?

SQL SELECT MAX
2022-04-17 14:41:26

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

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGERID
1001VAIBHAVIMISHRA65500PUNEORACLE1
1002VAIBHAVSHARMA60000NOIDAC#5
1003NIKHILVANI50500JAIPURFMW2
2001PRACHISHARMA55500CHANDIGARHORACLE1
2002BHAVESHJAIN65500PUNEFMW2
2003RUCHIKAJAIN50000MUMBAIC#5
3001PRANOTISHENDE55500PUNEJAVA3
3002ANUJAWANRE50500JAIPURFMW2
3003DEEPAMJAUHARI58500MUMBAIJAVA3
4001RAJESHGOUD60500MUMBAITESTING4
4002ASHWINIBAGHAT54500NOIDAJAVA3
4003RUCHIKAAGARWAL60000DELHIORACLE1
5001ARCHITSHARMA55500DELHITESTING4

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
SQL SELECT MAX

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:

CITYSALARY
MUMBAI60500
SQL SELECT MAX

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:

DEPARTMENTMAXIMUM_SALARY
JAVA58500
SQL SELECT MAX

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
SQL SELECT MAX

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:

CITYEMPLOYEE_SALARY
DELHI60000
NOIDA60000
SQL SELECT MAX

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:

DEPARTMENTEMPLOYEE_SALARY
NOIDA60000
JAIPUR65500
PUNE58500
PUNE65500
MUMBAI60500
SQL SELECT MAX

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:

DEPARTMENTEMPLOYEE_SALARY
C#60000
FMW65500
JAVA58500
ORACLE65500
TESTING60500
SQL SELECT MAX

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:

DEPARTMENTEMPLOYEE_SALARY
NOIDA60000
JAIPUR65500
PUNE58500
PUNE65500
MUMBAI60500
SQL SELECT MAX

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:

DEPARTMENTSALARY
C#60000
JAVA58500
ORACLE60000
TESTING55500
SQL SELECT MAX