? MySQL `MAX()`

? Quick Overview

The MAX() aggregate function in MySQL returns the largest value of a given column. It's commonly used with numeric types, dates, or any comparable values. Use it alone to get a single maximum across a set, or with GROUP BY to get maxima per group.

? Key Concepts

Aggregate function: Operates on a set of rows to return a single value.
NULL handling: MAX() ignores NULL values.
Usage with GROUP BY: Combine with GROUP BY to compute maxima per group.
Ordering vs MAX: To get the entire row with the maximum value, use subqueries or JOINs — MAX() only returns the value, not the row.

? Syntax / Theory

Basic syntax:

SELECT MAX(column_name) FROM table_name;
With GROUP BY: SELECT group_col, MAX(value_col) FROM table_name GROUP BY group_col;

? Code Examples

? View Code Example — Simple MAX()

// Get the highest salary from the employees table
SELECT MAX(salary) AS max_salary
FROM employees;

? View Code Example — MAX() with GROUP BY

// Get the maximum sale amount per region
SELECT region, MAX(sale_amount) AS max_sale
FROM sales
GROUP BY region;

? View Code Example — Get full row(s) having the MAX value (classic pattern)

// Return the employee(s) who have the maximum salary
SELECT e.*
FROM employees e
JOIN (
SELECT MAX(salary) AS max_salary
FROM employees
) m ON e.salary = m.max_salary;

? View Code Example — MAX() with DISTINCT

// MAX(DISTINCT ...) is allowed but usually redundant;
example shown for completeness
SELECT MAX(DISTINCT score) AS top_unique_score
FROM quiz_results;

? Live Output / Explanation

Example 1: Simple MAX()

Query: SELECT MAX(salary) AS max_salary FROM employees;

Explanation: Returns the single highest numeric value found in the salary column. NULL values are ignored. If the table is empty, result is NULL.

Example 2: MAX() with GROUP BY

Query: SELECT region, MAX(sale_amount) AS max_sale FROM sales GROUP BY region;

Explanation: For each distinct region, MySQL computes the largest sale_amount. Useful for leaderboards, summaries, and reporting.

Example 3: Getting complete rows

Pattern: Use a subquery to compute the max value, then join back to the main table to retrieve full rows that match that value. This returns all rows that tie for the maximum.

? Use Cases

Reporting highest sales, top scores, latest dates (using date columns), maximum ratings.
Dashboard summaries showing leaders per region, category, or team.
Data validation checks – find records with unexpectedly high values.

? Tips & Best Practices

Remember that MAX() ignores NULL — ensure NULLs are intended to be excluded.
To retrieve the row with the max value, avoid relying solely on ORDER BY ... LIMIT when you need ties — use the JOIN/subquery pattern to include duplicates with the same max value.
When using indexed columns, MAX() can be fast if MySQL can optimize via index scans (especially for indexed numeric/date columns).
Be explicit about column types: comparing strings vs numbers can yield unexpected results for MAX() on textual data.

? Try It Yourself / Practice Tasks

Create a table products(id, name, price, category) and insert sample data; write a query to find the most expensive product overall.
For the same table, find the most expensive product per category.
Given a scores(student_id, test_id, score) table, write a query to list students who achieved the maximum score on any test.
Experiment: what happens when all values in the target column are NULL? Verify the returned results.

MySQL MAX() Function

? MySQL MAX()