SQL AGGREGATE FUNCTIONS – MAX

SQL AGGREGATE FUNCTIONS – MAX

SQL has 5 different aggregate functions (MIN, MAX, COUNT, SUM & AVG). Here we will be looking at the MAX function. The MAX function returns the maximum value of a column in the table. 

SYNTAX – 

SELECT MAX(column1) FROM table_name WHERE condition;

Usually, the aggregate functions are used in conjunction with the GROUP BY/ HAVING clause, which gives even more meaningful results. The syntax for the query using MAX function using GROUP BY / HAVING clause is mentioned below –

SELECT column1, MAX(column2) FROM table_name WHERE condition1 GROUP BY column1 HAVING condition2;

At first, these might look little complicated, but once we go over few examples, it will make more sense. 



Illustration – Consider the below employee table in the database which stores the information pertaining to employees in a company –

 

IDemployee_nameagesalaryDepartmentmanager_name
1Jimmy3280000SalesVijay
2James3585000SalesVijay
3Helen3382000HRKrish
4Mary2678000QualityWarner
5William3390000HRKrish
6Vijay38105000SalesRoger
7Krish38105000HRRoger

Query 1 – What is the maximum salary earned by any employee in ABC company?

  SELECT MAX(salary) FROM employee;

MAX(salary)

————— 

105000

Query 2 – What is the maximum salary earned by any employee in ABC company whose age is less than 35 years?

  SELECT MAX(salary) FROM employee WHERE age<35;

MAX(salary)

————— 

90000

Query 3 – What is the maximum salary earned by employees in ABC company by each department.

  SELECT Department, MAX(salary) FROM employee GROUP BY Department;

DepartmentMAX(Salary)
Sales85000
HR105000
Quality78000

 

Query 4 – What is the maximum salary earned by employees in ABC company by each department that gets less than 90000 as salary.

  SELECT Department, MAX(salary) FROM employee GROUP BY Department HAVING MAX(salary) < 90000;

DepartmentMAX(Salary)
Sales85000
HR82000
Quality78000

 

 

 

Leave A Reply

Your email address will not be published. Required fields are marked *