MySQL MAX() Function

Introduction

In this chapter, we will learn about the MAX() function in MySQL. The MAX() function is an aggregate function that returns the maximum value of a specified column. It is widely used to find the highest value in a dataset, such as the maximum salary, maximum age, or highest score. We will cover the syntax for the MAX() function, examples of its usage, and important considerations for using it in MySQL.

Syntax

The basic syntax for using the MAX() function in MySQL is:

SELECT MAX(column_name)
FROM table_name
[WHERE condition];

Example: Find the Maximum Salary

SELECT MAX(salary) AS maximum_salary
FROM employees;

Full Example

Let’s go through a complete example where we create a database and table, insert data, and demonstrate the usage of the MAX() function.

1. Create a Database and Table

CREATE DATABASE company;
USE company;

CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

INSERT INTO employees (first_name, last_name, department, salary) VALUES
('Rahul', 'Sharma', 'Sales', 50000.00),
('Priya', 'Singh', 'Marketing', 60000.00),
('Amit', 'Kumar', 'Sales', 55000.00),
('Neha', 'Verma', 'IT', 58000.00),
('Sahil', 'Mehta', 'IT', 62000.00),
('Kiran', 'Patel', 'Sales', 53000.00);

2. Using MAX()

Example: Find the Maximum Salary

SELECT MAX(salary) AS maximum_salary
FROM employees;

Output

Example: Find the Maximum Salary by Department

You can use the MAX() function with the GROUP BY clause to find the maximum salary for each department.

SELECT department, MAX(salary) AS maximum_salary
FROM employees
GROUP BY department;

Output

Example: Find the Maximum Salary for a Specific Department

You can use the MAX() function with the WHERE clause to find the maximum salary for employees in a specific department.

SELECT MAX(salary) AS sales_maximum_salary
FROM employees
WHERE department = 'Sales';

Output

Using MAX() with Other Aggregate Functions

The MAX() function can be used in combination with other aggregate functions for more complex queries.

Example: Find the Maximum and Minimum Salary by Department

SELECT department,
       MAX(salary) AS maximum_salary,
       MIN(salary) AS minimum_salary
FROM employees
GROUP BY department;

Output

Important Considerations

• NULL Values: The MAX() function ignores NULL values. Ensure that the column being evaluated does not have NULL values if this is not the desired behavior.
• Data Types: The MAX() function works with numeric, string, and date data types. Ensure that the column being evaluated contains compatible values.
• Performance: The MAX() function can impact performance, especially on large datasets. Consider indexing columns used in the WHERE clause to improve performance.

Conclusion

The MAX() function in MySQL is used for finding the highest value in a specified column. This chapter covered the syntax for using the MAX() function, provided examples of its usage, and discussed important considerations.