The MAX() function in MySQL is an aggregate function used to retrieve the maximum value from a column. It is commonly used to find the highest value in a dataset, such as the maximum salary, the highest score, or the most recent date. The function operates on numeric, date, and time data types.

Syntax

MAX(column_name)

• column_name: The name of the column from which to retrieve the maximum value.

Usage

1. Basic Maximum Value Retrieval

   To find the highest value in a numeric column:

   SELECT MAX(salary) AS highest_salary
   FROM employees;
   

   This query retrieves the highest salary from the employees table and labels it as highest_salary.

2. Maximum with Conditions

   To find the maximum value based on specific conditions, use the WHERE clause:

   SELECT MAX(amount) AS max_sales
   FROM sales
   WHERE status = 'Completed';
   

   This query retrieves the maximum sales amount where the status is 'Completed'.

3. Maximum with Grouping

   To find the maximum value for each group of rows, use the GROUP BY clause:

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

   This query retrieves the highest salary within each department.

4. Maximum with HAVING Clause

   To filter groups based on the maximum value, use the HAVING clause:

   SELECT department, MAX(salary) AS highest_salary
   FROM employees
   GROUP BY department
   HAVING MAX(salary) > 100000;
   

   This query retrieves departments where the highest salary exceeds 100,000.

5. Maximum for Non-Numeric Data

   The MAX() function can also be used with date and time columns:

   SELECT MAX(order_date) AS latest_order
   FROM orders;
   

   This query retrieves the most recent order date from the orders table.

6. Handling NULL Values

   The MAX() function ignores NULL values in the specified column. If a column contains NULL values, they are not included in the maximum value calculation:

   SELECT MAX(bonus) AS max_bonus
   FROM employees;
   

   If some bonus values are NULL, they are excluded from the maximum calculation.

Best Practices

• Use Indexes: Ensure that columns used in aggregate functions are indexed to improve query performance, especially with large datasets.
• Combine with Other Aggregates: Use MAX() in combination with other aggregate functions like MIN(), AVG(), and SUM() for comprehensive data analysis.
• Filter and Group Data: Use the WHERE and GROUP BY clauses to filter and organize data before applying MAX() for more precise results.
• Handling Large Datasets: For very large datasets, consider optimizing queries and using indexed columns to improve performance.