How to Use MySQL MAX() Function: Syntax and Examples

The MAX() function in MySQL may be one of the simplest aggregate functions you’ll ever use, but it drives the numbers that shape billion-dollar decisions. It’s the statement behind every “highest revenue” slide, every “record traffic” alert, and every “latest transaction” in compliance.

However, this function is often misunderstood. Developers run into errors when combining it with WHERE, misuse it with GROUP BY, or assume it returns rows instead of values. Knowing how and when to apply it correctly is what separates a reliable report from a misleading one.

In this guide, you’ll learn how to use it effectively: the syntax, practical examples, and advanced patterns with WHERE, GROUP BY, and JOINs. You’ll also see common pitfalls and how a MySQL IDE can make the process smoother.

What is the MySQL MAX() function?

The MySQL MAX function is an aggregate function that returns the largest value from a given column or expression. It scans all rows within the query’s scope and identifies the highest value, whether it comes from numbers, dates, or even strings.

Because it’s an aggregate function, MAX() condenses multiple rows of data into a single result. For example, you might use it to find the highest salary in an employee table, the most recent order date, or the alphabetically last name in a customer list.

MySQL supports applying MAX() to different data types:

• Numeric columns: MAX() returns the highest number (e.g., the maximum sales amount).
• Date/time columns: MAX() returns the most recent date or timestamp.
• String columns: MAX() returns the highest value based on the lexical (alphabetical) order.

In practice, it is one of the most frequently used MySQL aggregate functions. But to understand how to use the MySQL MAX function, you must first look at its syntax.

Syntax of the MAX() function in MySQL

The MySQL MAX() function syntax is as follows:

MAX(expression);

In this syntax, expression can be a column name or any valid expression. MySQL evaluates all values in the specified set and returns the single largest one.

For example, retrieving the highest salary in the employees table is as direct as follows:

SELECT MAX(salary) AS highest_salary 
FROM employees;

Handling NULL values

The behavior of MAX() with NULL values is precise:

• Rows containing NULL are ignored during evaluation.
• If every row in the set is NULL, the result is NULL.
• NULL is never considered a candidate for the maximum value.

This deterministic behavior ensures predictable results across numeric, date, and string columns without requiring extra filters, though developers should design queries with the possibility of a NULL result in mind.

Now that you understand the syntax of the MAX function, the next step is to see how it works in the real world.

Practical examples of the MySQL MAX() function

The MAX() function is not theoretical: it is a workhorse in reporting and analytics. Here are definitive patterns you will encounter in production systems.

Simple example with a numeric column

Find the highest salary in the employees table:

SELECT MAX(salary) AS highest_salary 
FROM employees;

Use case: Identify the top-paid employee instantly, which is a standard query in HR and payroll systems.

Example with a date column

Get the most recent order date:

SELECT MAX(order_date) AS latest_order 
FROM orders;

Use case: Ensure accurate reporting on customer activity by getting the single most recent transaction date.

Example with a string column

Return the alphabetically last customer name:

SELECT MAX(last_name) AS last_customer 
FROM customers;

Use case: With MAX() not limited to numbers, compare strings by collation, getting the last value in the sort order.

MAX() with aliases for clarity

Label results in a report:

SELECT MAX(total_amount) AS peak_invoice_amount 
FROM invoices;

Use case: Use clear aliases like peak_invoice_amount to make query results instantly interpretable in dashboards and exports.

MAX() on an expression

Evaluate the maximum order value directly from an expression:

SELECT MAX(price * quantity) AS max_order_value 
FROM order_items;

Use case: Calculate peak sales without relying on pre-computed columns.

Key takeaways:

• NULL values are ignored; if every row is NULL, the result is NULL.
• The function works on numbers, dates, and strings with deterministic behavior.
• For strings, results depend on the column’s collation rules.

The MySQL MAX() function examples above form the foundation for every advanced use of MAX() in business intelligence, financial reporting, and analytics.

Next, let’s see why MAX() can’t go in a WHERE clause, and how to handle it correctly.

Using MySQL MAX() with a WHERE clause

Developers often try to use MAX() in a WHERE clause, but MySQL blocks it. Aggregates are evaluated after filtering, so they can’t appear there. The fix is to compute the maximum first and then filter against it.

Incorrect usage

Here is an example of an invalid query that tries to use MAX() in a WHERE clause. MySQL rejects it with the following error: Invalid use of group function.

SELECT * 
FROM employees 
WHERE salary = MAX(salary);

This fails because the WHERE clause is evaluated before aggregate functions. At that stage, MySQL has not yet computed the maximum value.

Correct approaches

Because MAX() can’t be used in WHERE, you must calculate the maximum first and then filter. The following patterns cover the most common and reliable ways to do it.

1. Scalar subquery (recommended)

SELECT * 
FROM employees 
WHERE salary = (SELECT MAX(salary) FROM employees); 

Returns all employees who share the highest salary.

2. Subquery with filter conditions

SELECT * 
FROM employees 
WHERE department_id = 10 
  AND salary = ( 
      SELECT MAX(salary) 
      FROM employees 
      WHERE department_id = 10 
  );

Finds the highest-paid employee(s) in a specific department.

3. Derived table with JOIN

SELECT e.* 
FROM employees AS e 
JOIN ( 
  SELECT MAX(salary) AS max_salary 
  FROM employees 
) AS m 
  ON e.salary = m.max_salary;

Useful when combining MAX() with other aggregated results.

4. ORDER BY with LIMIT

SELECT * 
FROM employees 
ORDER BY salary DESC 
LIMIT 1;

Quick way to fetch the top row only. Be aware that this method ignores ties unless you add secondary sorting logic.

Key points:

• MAX() cannot be placed in WHERE because of SQL’s evaluation order.
• Always compute the maximum first (subquery or derived table) before filtering.
• Use ORDER BY … LIMIT when only a single row is required.
• Add indexes to the aggregated column to improve performance.

However, while subqueries solve MAX() in WHERE, the real power comes when using MySQL MAX function with GROUP BY to find maximums per group.

Using MySQL MAX() with GROUP BY

The following examples of the MAX() function in MySQL use GROUP BY to calculate maximum values across categories.

Example 1: Finding the highest salary per department

SELECT 
  department_id, 
  MAX(salary) AS max_salary 
FROM employees 
GROUP BY department_id;

This query returns one row per department, showing its highest salary.

Example 2: Adding descriptive fields

SELECT 
  e.department_id, 
  d.department_name, 
  MAX(e.salary) AS max_salary 
FROM employees AS e 
JOIN departments AS d 
  ON d.department_id = e.department_id 
GROUP BY e.department_id, d.department_name;

By grouping on both ID and name, you add readable department details to the report.

Example 3: Selecting the row(s) that match the maximum

SELECT 
  e.department_id, 
  e.employee_id, 
  e.employee_name, 
  e.salary 
FROM employees AS e 
JOIN ( 
  SELECT department_id, MAX(salary) AS max_salary 
  FROM employees 
  GROUP BY department_id 
) AS m 
  ON m.department_id = e.department_id 
  AND m.max_salary = e.salary; 

This pattern retrieves the employee or employees who actually earn the maximum salary in each department.

Example 4: Handling ties and picking a single winner

WITH ranked AS ( 
  SELECT 
    e.department_id, 
    e.employee_id, 
    e.employee_name, 
    e.salary, 
    ROW_NUMBER() OVER ( 
      PARTITION BY e.department_id 
      ORDER BY e.salary DESC, e.employee_id DESC 
    ) AS rn 
  FROM employees AS e 
) 
SELECT department_id, employee_id, employee_name, salary 
FROM ranked 
WHERE rn = 1;

Here, window functions assign a ranking, ensuring exactly one row is returned per department, even if multiple employees share the maximum salary.

Using MySQL MAX() with JOINs

MAX() is especially powerful when you need the maximum value per entity from a related table. This section provides examples of combining MAX() with JOINs to achieve that.

Example 1: Finding the most recent order date per customer

SELECT 
  c.customer_id, 
  c.customer_name, 
  MAX(o.order_date) AS last_order_date 
FROM customers AS c 
LEFT JOIN orders AS o 
  ON o.customer_id = c.customer_id 
GROUP BY c.customer_id, c.customer_name;

This query returns customers' most recent order date with one row per customer (customers with no orders return NULL).

Example 2: Getting the actual latest order row per customer

SELECT 
  c.customer_id, 
  c.customer_name, 
  o.order_id, 
  o.order_date, 
  o.total_amount 
FROM customers AS c 
JOIN ( 
  SELECT customer_id, MAX(order_date) AS max_order_date 
  FROM orders 
  GROUP BY customer_id 
) AS m 
  ON m.customer_id = c.customer_id 
JOIN orders AS o 
  ON o.customer_id = m.customer_id 
 AND o.order_date = m.max_order_date; 

This query joins the per-customer MAX(order_date) back to orders to fetch the full row. If multiple orders share the same latest date, all are returned; add a tiebreaker (e.g., the highest order_id) if you need a single row.

Example 3: Finding the most recent price per product

SELECT 
  p.product_id, 
  p.product_name, 
  ph.price, 
  ph.effective_date AS price_effective_date 
FROM products AS p 
JOIN ( 
  SELECT product_id, MAX(effective_date) AS max_eff 
  FROM price_history 
  GROUP BY product_id 
) AS m 
  ON m.product_id = p.product_id 
JOIN price_history AS ph 
  ON ph.product_id = m.product_id 
 AND ph.effective_date = m.max_eff; 

This query returns each product’s current price by joining the most recent effective_date from price_history to the corresponding row.

Example 4: Finding the highest historical price per product

SELECT 
  p.product_id, 
  p.product_name, 
  MAX(ph.price) AS peak_price 
FROM products AS p 
JOIN price_history AS ph 
  ON ph.product_id = p.product_id 
GROUP BY p.product_id, p.product_name;

Here, the MAX() function computes the maximum recorded price (not necessarily the latest). Choose this when the business question is “peak price,” not “current price.”

Key points:

• Use MAX() with JOINs when working across related tables, such as customers–orders or products–price history.
• MAX() on dates answers “What is the latest?” while MAX() on values answers “What is the highest?”
• To return the full row with the maximum, compute the maximum in a subquery and join it back.

Beyond JOINs, MAX() proves its value in everyday reporting and analytics.

Common use cases of the MySQL MAX() function

The MAX() function is central to real-world databases because it quickly identifies the highest, latest, or peak value. Here are its most common applications.

Reporting and business intelligence

Executives rely on top-line numbers: highest sales, largest expenses, biggest invoices. These numbers drive dashboards and executive KPIs.

Examples:

-- Highest daily sales 
SELECT MAX(daily_total) AS peak_sales 
FROM sales_summary; 
 
-- Largest single expense 
SELECT MAX(amount) AS largest_expense 
FROM expenses; 
 
-- Biggest invoice ever issued 
SELECT MAX(total_amount) AS biggest_invoice 
FROM invoices;

Analytics and KPIs

Operations teams use MAX() to track performance limits: login spikes, sensor peaks, or top bids in an auction.

Examples:

-- Most logins by a user today 
SELECT MAX(login_count) AS max_logins 
FROM user_login_stats 
WHERE login_date = CURRENT_DATE(); 
 
-- Highest temperature recorded 
SELECT MAX(reading_value) AS max_temp 
FROM sensor_readings 
WHERE reading_type = 'temperature'; 
 
-- Top bid in an auction 
SELECT MAX(bid_amount) AS top_bid 
FROM bids 
WHERE auction_id = 42;

With these queries, MAX() surfaces outliers that can trigger alerts, thresholds, or KPI tracking.

Trend analysis

MAX() helps spot turning points: the month with the highest revenue, the day with record sales, or the all-time peak for a product. These highs expose seasonality, demand surges, and long-term growth patterns.

Examples:

-- Peak monthly revenue per year 
SELECT 
  YEAR(order_date) AS year, 
  MAX(month_revenue) AS peak_revenue 
FROM revenue_by_month 
GROUP BY YEAR(order_date); 
 
-- All-time peak sales for each product 
SELECT 
  product_id, 
  MAX(daily_sales) AS record_sales 
FROM product_sales 
GROUP BY product_id; 

Key takeaway: Whenever the business question is “What was the highest, latest, or peak value?”, the MAX() function is the authoritative answer.

MAX() is powerful, but it has limits: performance, NULLs, and edge cases to watch.

Limitations and considerations

While MAX() is straightforward, developers often run into pitfalls when applying it at scale or in edge cases. Understanding these limitations and how to address them keeps queries reliable and efficient.

Performance with large datasets

MAX() requires scanning the target column, which can become expensive on very large tables. Without optimization, it may trigger full table or index scans.

How to fix: Create indexes on the column used with MAX() (e.g., (department_id, salary) or (order_date)). This lets MySQL locate maximum values quickly without scanning every row.

Multiple maximum values

By design, MAX() returns a single value per group. It does not tell you which row, or rows, produced that value. If multiple rows share the maximum, only the value is visible.

How to fix: Use a subquery or join back to the base table to retrieve all rows with the maximum. To enforce a single winner per group, apply MySQL window functions such as ROW_NUMBER().

NULLs and mixed data types

MAX() ignores NULL values. If every row in the set is NULL, the result is NULL. Mixed data types in the same column (e.g., numbers stored as strings) can also produce unexpected ordering, especially with string collations.

How to fix: Ensure the column uses consistent data types and handle NULL explicitly with conditions like WHERE column IS NOT NULL when appropriate.

Finally, let’s look at common MAX() errors and how to resolve them.

Troubleshooting MySQL MAX() errors

Even though MAX() is simple, it’s easy to misuse. Below are the most common mistakes developers encounter, why they happen, and how to fix them.

“MAX() in WHERE clause” error

A frequent beginner error is trying to use MAX() directly in the WHERE clause:

SELECT * 
FROM employees 
WHERE salary = MAX(salary);

This fails because WHERE is evaluated before aggregate functions.

How to fix: Use a subquery or a derived table:

SELECT * 
FROM employees 
WHERE salary = (SELECT MAX(salary) FROM employees);

Wrong results with GROUP BY

Another common issue is mixing aggregated and non-aggregated columns incorrectly:

SELECT department_id, employee_name, MAX(salary) 
FROM employees 
GROUP BY department_id;

Depending on the SQL mode, MySQL may return arbitrary values for employee_name.

How to fix: Ensure only grouped columns and aggregates are selected:

SELECT department_id, MAX(salary) 
FROM employees 
GROUP BY department_id;

Or, if you need the employee name too, join back to the table:

SELECT e.department_id, e.employee_name, e.salary 
FROM employees e 
JOIN ( 
  SELECT department_id, MAX(salary) AS max_salary 
  FROM employees 
  GROUP BY department_id 
) m 
  ON e.department_id = m.department_id 
AND e.salary = m.max_salary;

Unexpected NULL in result

Sometimes MAX() returns NULL, which surprises developers:

SELECT MAX(salary) 
FROM employees 
WHERE department_id = 99;

This happens when:

• All values are NULL.
• Or, the WHERE condition filters out all rows.

How to fix: Handle explicitly with COALESCE() or conditional logic:

SELECT COALESCE(MAX(salary), 0) AS max_salary 
FROM employees 
WHERE department_id = 99;

How dbForge Studio for MySQL helps with MAX() queries

Knowing how to write MAX() queries is one thing; managing them efficiently in day-to-day work is another. Developers and analysts often need to join multiple tables, troubleshoot unexpected results, or prepare maximum values for reports. Doing all of this manually can be slow and error-prone.

dbForge Studio for MySQL addresses this by providing capabilities that make building, analyzing, and optimizing queries with MAX() functions faster and more reliable. Here are the functionalities most relevant to working with MAX():

• Visual Query Builder: Instead of hand-writing JOINs or subqueries, you can construct MySQL queries visually, apply MAX(), and preview results instantly. This is especially useful for “latest order per customer” or “highest value per group” scenarios.

• Smart code completion: SQL Editor suggests aggregate functions like MAX() and assists with column names, aliases, and clauses. This reduces errors when combining MAX() with GROUP BY, JOIN, or filters.

• Data analysis: Query results, including those from MAX(), can be explored in grids, pivot tables, and charts. This makes it easier to analyze peaks (e.g., maximum sales per department) and present them clearly.

• Export and reporting: Results from MAX() queries, such as highest sales, latest transactions, or peak usage values, can be exported directly to Excel, CSV, or PDF for reporting or further analysis.

• Performance optimization: Execution plans and query profiling help identify bottlenecks, ensuring that MAX() queries remain efficient on large datasets where full scans might otherwise slow performance.

With these features, dbForge Studio takes the manual overhead out of maximum-value queries and turns them into a streamlined part of your reporting and analytics workflow.

Download a free trial of dbForge Studio for MySQL and see how quickly you can build and optimize MAX() queries.

Conclusion

The MAX() function may be simple, but it solves critical business questions: What’s the highest value? The most recent record? The peak over time? Throughout this guide, we covered its syntax, practical examples, and advanced usage with WHERE, GROUP BY, and JOINs. We also looked at common use cases, potential pitfalls, and how to troubleshoot errors effectively.

Used correctly, MAX() helps you surface the data points that matter most, whether it’s the largest invoice, the top sale, or the latest transaction. And with dbForge Studio for MySQL, you can go beyond writing queries manually: build them visually, analyze results faster, and keep performance under control.

Try dbForge Studio for MySQL to write and execute MAX() queries faster.

FAQ

SELECT department_id, 
       MAX(salary) AS highest_salary, 
       COUNT(*)    AS employee_count 
FROM employees 
GROUP BY department_id;