SQL MAX()

MIN() answered "what's the lowest salary?" MAX() answers the opposite question, Beacon Robotics' finance team also wants to know the highest salary on the books, for budget planning on the other end of the scale.

Learning Objectives

Use MAX() to find the largest value in a column.
Recognize MAX() works on text and dates, not just numbers, same as MIN().
Avoid the most common mistake made when trying to find who holds the maximum value.

A Column

Every salary value

MAX()

Compares every value

Largest Value

One number back

MAX() on Numbers

The mirror image of MIN(): scans every non-NULL value and returns the largest one. Same single-value result shape as every aggregate function covered so far.

MySQL / MariaDB / PostgreSQL / SQLite / SQL Server / Oracle

SELECT MAX(salary) AS highest_salary
FROM employees;

MAX() on Dates and Text

Same rule as MIN(): MAX(hire_date) returns the most recent date (furthest forward in time), and MAX(department) returns whichever department name sorts last alphabetically.

MySQL / MariaDB / PostgreSQL / SQLite / SQL Server / Oracle

SELECT MAX(hire_date) AS most_recent_hire
FROM employees;

SELECT MAX(department) AS last_alphabetically
FROM employees;

Finding Who Holds the Maximum

This is the single most common MAX() mistake, and it's worth its own section: MAX(salary) returns the number 98000, not the employee earning it. To get the actual row, sort with ORDER BY salary DESC and take the top result with LIMIT (or FETCH FIRST on Oracle), both covered back in Module 1. No new syntax needed, just the right tool for the job.

MySQL / MariaDB / PostgreSQL / SQLite / SQL Server

SELECT first_name, department, salary
FROM employees
ORDER BY salary DESC
LIMIT 1;

Oracle

SELECT first_name, department, salary
FROM employees
ORDER BY salary DESC
FETCH FIRST 1 ROW ONLY;

Try It Yourself

Write a query that finds the name of the most recently hired employee in the Engineering department.

Answer (MySQL / MariaDB / PostgreSQL / SQLite / SQL Server)

SELECT first_name, hire_date
FROM employees
WHERE department = 'Engineering'
ORDER BY hire_date DESC
LIMIT 1;

Frequently Asked Questions

Why doesn't SELECT first_name, MAX(salary) FROM employees just work?

Same reason it fails for MIN(): first_name is a per-row value, MAX(salary) is one summarized value for the entire result, and SQL has no way to decide which employee's name to pair it with. It's the mixing-aggregates-with-raw-columns mistake from the aggregate functions overview, applying here too.

Is there a faster way to get both the maximum value and the row it belongs to in one query, without ORDER BY and LIMIT?

Not yet in this course, that typically involves a subquery or a window function, both covered later (Modules 6 and 10). ORDER BY plus LIMIT is the simplest, most portable approach at this point and is genuinely how this is solved in practice most of the time.

Common Beginner Mistakes

Treating MAX(salary) as if it returns the whole row

It returns exactly one value, the number itself. Many beginners write SELECT * FROM employees WHERE salary = MAX(salary), which isn't valid syntax, MAX() can't be used directly inside WHERE like that, since WHERE filters individual rows before any aggregation happens. The correct approach is ORDER BY salary DESC LIMIT 1, shown above.

Forgetting MAX() also works on dates

MAX(hire_date) for "most recent hire" is a completely valid and common use, not limited to the numeric examples usually shown first when MAX() is introduced.

Best Practices

Use MAX() when only the value itself is needed, not which row it came from.
Use ORDER BY + LIMIT/TOP/FETCH FIRST when the actual row holding the maximum matters.
Never try to use MAX() directly inside a WHERE clause, it isn't valid; WHERE filters rows before aggregation happens.

Interview Questions

Why is SELECT * FROM employees WHERE salary = MAX(salary) invalid SQL?

WHERE filters individual rows before any aggregate function has a chance to run across the result set, so an aggregate function like MAX() can't be referenced inside the same query's WHERE clause this directly. The valid alternatives are ORDER BY salary DESC LIMIT 1 (simplest, works now) or a subquery like WHERE salary = (SELECT MAX(salary) FROM employees), which is valid but relies on subquery syntax covered in Module 6.

If two employees are tied for the highest salary, what does MAX(salary) return, and what does ORDER BY salary DESC LIMIT 1 return?

MAX(salary) still returns a single number, the tied salary value, ties don't affect it since it's just comparing values, not rows. ORDER BY salary DESC LIMIT 1 returns only one of the two tied employees, arbitrarily (whichever the database happens to return first), which is worth knowing: LIMIT 1 doesn't guarantee a specific row when there's a tie at the cutoff.

Summary

MAX() returns the largest value in a column, numeric, text, or date, mirroring MIN() exactly. Finding the specific row behind that maximum value requires ORDER BY DESC and LIMIT, not MAX() directly, and MAX() can never be referenced inside WHERE.

What's Next?

The next lesson covers COUNT() in more depth than the overview did, including COUNT(DISTINCT column) for counting unique values.

</>

Ready to Practice SQL?

Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.

SQLite / PostgreSQL

Fast, no server, no ads.

Open DB Fiddle ↗

MySQL / SQL Server

Switch databases with one dropdown.

Open SQL Fiddle ↗

Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.