SQL MIN() and MAX() Functions Explained with Examples

A report asks for the lowest and the highest salary a table holds, and each of those numbers takes one function call. SQL MAX() returns the largest value in a column and SQL MIN() the smallest, both written as one column name inside the brackets: MAX(column_name) or MIN(column_name). Both are aggregate functions, so they skip rows where the column is NULL and return NULL only when the group they run over holds no non-NULL value at all[1].

The SQL MIN() function

MIN() takes one column and returns the smallest value it finds across every row the query selects. It sits in the SELECT list, and the column it reads goes inside the brackets.

SELECT MIN(column_name)
FROM table_name;

Every query on this page runs against the same three-row Employees table.

To find the lowest salary in the table:

SELECT MIN(Salary)
FROM Employees;

The query returns one row with one column:

50000 is John Doe's salary, the smallest of the three. MIN() collapses the whole table into a single value, so the result says nothing about which employee earns it.

Rows where Salary is NULL are skipped rather than read as zero. MIN() returns NULL only when the group contains no non-NULL value, which is the documented behaviour in SQLite[1], in MySQL[2] and in SQL Server[3] alike.

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download

The SQL MAX() function

MAX() is the mirror image: same syntax, same NULL rule, and it returns the largest value in the column instead of the smallest.

SELECT MAX(Salary)
FROM Employees;

60000 is Jane Smith's salary, the highest of the three. SQL Server states the return type plainly: MAX returns a value of the same type as the expression[3] it was given, so a MAX() over an integer column comes back as an integer, and one over a date column comes back as a date.

Neither accepts more than one column. Comparing two columns of the same row takes a row-level function such as GREATEST or LEAST, not an aggregate.

Selecting the full row that holds the MIN or MAX value

Knowing that the highest salary is 60000 is rarely the end of the question. The usual follow-up is which employee earns it, and that needs the whole row. The portable pattern is a subquery in the WHERE clause: compute the aggregate once, then select the rows that match it.

SELECT EmployeeID, FirstName, LastName, Salary
FROM Employees
WHERE Salary = (SELECT MAX(Salary) FROM Employees);

Swapping MAX for MIN returns John Doe on 50000 instead. Two details decide whether the query is right for your data. Ties return every matching row, so two employees on 60000 both come back. And the subquery is evaluated over the whole table unless you repeat the outer filter inside it, so a WHERE Department = 'IT' outside still compares against the company-wide maximum.

For the highest-paid employee per department, the same subquery has to be correlated:

SELECT EmployeeID, FirstName, LastName, Department, Salary
FROM Employees e
WHERE Salary = (SELECT MAX(Salary)
                FROM Employees
                WHERE Department = e.Department);

Two engine-specific shortcuts do the same job with less typing. PostgreSQL offers DISTINCT ON[4], which keeps the first row of each group after an ORDER BY, and SQL Server exposes MIN and MAX as window functions through an OVER clause[3], which computes the aggregate per partition while keeping the individual rows intact.

SELECT DISTINCT ON (Department)
       Department, EmployeeID, FirstName, Salary
FROM Employees
ORDER BY Department, Salary DESC;

MIN() and MAX() with GROUP BY

Without GROUP BY, MIN() and MAX() reduce the entire table to one row. Adding a GROUP BY clause changes the unit of aggregation: the engine forms one group per distinct value of the grouping column and returns one minimum and one maximum per group.

SELECT Department,
       MIN(Salary) AS MinSalary,
       MAX(Salary) AS MaxSalary
FROM Employees
GROUP BY Department;

The Employees table holds two departments, so the result holds two rows:

HR contains only Jane Smith, so its minimum and maximum are the same number. IT contains John Doe on 50000 and Sam Brown on 55000, so the two differ.

This is also the rule that governs which other columns you are allowed to select. A query such as SELECT FirstName, MIN(Salary) FROM Employees is rejected by PostgreSQL and by SQL Server, because FirstName is neither grouped nor aggregated and the engine has no way to decide which of the three names to print. MySQL rejects it too when ONLY_FULL_GROUP_BY[5] is enabled, and MySQL enables it by default. Every column in the SELECT list therefore has to be either inside an aggregate or named in the GROUP BY.

MIN() and MAX() with ORDER BY

ORDER BY sorts the rows a query returns, and a grouped query can be sorted by the aggregate it just computed. That is how the cheapest or the most expensive group reaches the top without you scanning the output by eye.

SELECT Department, MAX(Salary) AS MaxSalary
FROM Employees
GROUP BY Department
ORDER BY MaxSalary DESC;

Drop the DESC and ascending order returns, putting IT on 55000 first. The alias is safe in this clause: PostgreSQL allows a query output column to be referenced by name or by ordinal number[4] in GROUP BY and in ORDER BY, so ORDER BY MaxSalary and ORDER BY MAX(Salary) mean the same thing. The HAVING clause is where that stops being true.

WHERE and HAVING with MIN() and MAX()

The two clauses run at different points in the query. WHERE filters individual rows before grouping happens, so an aggregate cannot appear in it: at that moment the groups do not exist yet. HAVING filters the grouped rows[4] after the aggregates have been computed, which is where a condition on MIN() or MAX() belongs.

This query is rejected by every engine, because MAX() is evaluated after WHERE:

SELECT Department, Salary
FROM Employees
WHERE Salary = MAX(Salary);

There are two correct rewrites, and they answer different questions. To filter rows against an aggregate of the whole table, put the aggregate in a subquery, which WHERE evaluates as an ordinary scalar value. That is the pattern shown under the full-row section above. To filter whole groups against their own aggregate, use HAVING instead. The HAVING clause is the only place a condition on MIN() or MAX() can go without a subquery:

SELECT Department, MAX(Salary) AS MaxSalary
FROM Employees
GROUP BY Department
HAVING MAX(Salary) > 55000;

Only HR clears the threshold, so IT drops out of the result entirely rather than coming back with a filtered-down maximum. That is the difference HAVING makes: it removes groups, it does not adjust them.

Note that the condition repeats MAX(Salary) rather than reusing the MaxSalary alias. Repeating the aggregate is the portable form, because PostgreSQL requires every column referenced in a HAVING condition to be a grouping column or to sit inside an aggregate[4], so the alias fails there. MySQL accepts the alias, and its own manual calls that an extension to standard SQL[5].

Combining MIN() and MAX() with COUNT, SUM and AVG

Aggregates compose freely. MIN() and MAX() can appear in the same SELECT list as each other and alongside COUNT, AVG and SUM, and each one is computed across all the rows making up each group.

SELECT Department,
       COUNT(*)    AS Headcount,
       MIN(Salary) AS MinSalary,
       MAX(Salary) AS MaxSalary,
       AVG(Salary) AS AvgSalary,
       SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY Department;

Two things in that result matter. COUNT(*) counts rows, including rows whose Salary is NULL, while the other four ignore those rows entirely, so a table with missing salaries can show a headcount of 5 and an AVG computed over 3 values. And AVG returns 52500 for IT, an exact halfway point, only because those two salaries average to a whole number. MIN and MAX return a value taken from the column, so the type that comes back is the column's own. An average is computed from the rows, so its value need not be one the column holds.

The PostgreSQL manual notes one trap that catches people combining aggregates over a filtered table: apart from COUNT, aggregate functions return NULL rather than zero when no rows are selected[6], so SUM over an empty result is NULL, not 0. Wrap it in COALESCE if a numeric zero is what downstream code expects. The same caveat applies to every PostgreSQL aggregate function.

MIN() and MAX() on text and date columns

MIN() and MAX() are not restricted to numbers. Any type with a defined sort order works, which in practice means character columns, date and timestamp columns, and in SQL Server also uniqueidentifier. SQL Server names the exception explicitly: MAX works on numeric, char, nchar, varchar, nvarchar, uniqueidentifier and datetime columns, but not on bit columns[3].

SELECT MIN(FirstName) AS FirstName_Low,
       MAX(FirstName) AS FirstName_High,
       MIN(HireDate)  AS EarliestHire,
       MAX(HireDate)  AS LatestHire
FROM Employees;

On a date column MIN() is the oldest date and MAX() the newest, so the earliest hire is John Doe in March 2019 and the latest is Jane Smith in July 2021.

On the text column the reading needs a caveat. Jane and Sam come back because of the column's collation rather than alphabetical order in the everyday sense. SQL Server puts it exactly: for character columns MAX finds the highest value in the collating sequence[3]. A case-sensitive collation sorts every uppercase letter before every lowercase one, so 'Zoe' can come back as the minimum and 'adam' as the maximum on data that looks alphabetically obvious. MySQL documents a further wrinkle: on ENUM and SET columns MIN() and MAX() compare by string value rather than by the position in the set[2], which is not how ORDER BY compares them.

If the result has to be alphabetical regardless of case, normalise inside the function: MIN(LOWER(FirstName)) sorts on the lowercased value, at the cost of returning the lowercased string rather than the stored one.

Checking MIN() and MAX() results in DbSchema

Most MIN() and MAX() mistakes are silent: a wrong number comes back looking exactly like a right one, because the query ran and returned a row. DbSchema puts the SQL and the result it produced on one screen.

Open the SQL Editor from the Editors menu, paste the statement, and click Execute Query. DbSchema runs it against the connected database and shows the row in the result table, and the SQL History pane records the statement it sent. That log is how you confirm the aggregate came from the rows you expected rather than from a filter that quietly removed some. The SQL Editor runs against the database; the editor itself is saved in the design model file, so the query is still there next time you open the model.

To assemble a grouped query without typing it, open the Query Builder, which is part of DbSchema Pro, and turn on Group By in its toolbar. Ticked columns without an aggregate become the GROUP BY columns, and right-clicking a column offers Aggregate with MIN, MAX, SUM, AVG and COUNT, so the grouping column and the aggregated column are picked from a list. The generated SQL updates as you tick and stays visible, ready to copy into application code once the numbers check out.

Common mistakes with MIN() and MAX()

Most of the errors above have one shape: the aggregate is computed at a different moment than you assumed. The condition goes in HAVING or in a subquery, never in WHERE. The HAVING condition repeats the aggregate rather than the alias. Every column outside an aggregate is named in the GROUP BY. And an empty result gives you NULL rather than zero, which then propagates through any arithmetic done on it.

One mistake is about the data instead. Reading MAX() on a text column as alphabetical order is wrong whenever the collation is case-sensitive or accent-sensitive, and the query gives no sign of it.

Practice questions

1. Return the highest and the lowest salary in the Employees table in one row, with readable column names.
2. Return only the departments whose maximum salary is above 55000, without using an alias in the condition.
3. Return the full row for the employee hired earliest, and say what your query does if two employees share that hire date.

MIN() and MAX() are the two simplest aggregates in SQL and the two that quietly go wrong most often, because a wrong grouping, a NULL column or an alias in the wrong clause all still return a number. Download DbSchema at https://dbschema.com/download.html, connect to your database, and run these queries in the SQL Editor, which is in the free Community Edition with the diagrams. The Query Builder that assembles a grouped query without typing it is part of DbSchema Pro.

FAQs

Do MIN() and MAX() work on columns that contain NULL?

Both functions ignore NULL rows and aggregate only the values that are present. They return NULL themselves only when the group they run over contains no non-NULL value at all, so a NULL result means no data rather than a zero.

Can MIN() and MAX() be used on text and date columns?

Any type with a defined sort order works. On a date column MIN() returns the earliest value and MAX() the latest. On a character column the comparison follows the column's collation, so the answer can differ from plain alphabetical order when that collation is case-sensitive or accent-sensitive.

Can MIN() and MAX() appear in the same query?

SELECT MIN(Salary), MAX(Salary) FROM Employees returns both bounds in a single row and reads the table once. COUNT, SUM and AVG sit in that list too, with one difference to watch: COUNT(*) counts every row while the value aggregates skip the NULL ones.

Why can't I use MIN() or MAX() in a WHERE clause?

WHERE is applied to individual rows before the groups exist, and an aggregate has nothing to aggregate at that point. Move the condition to HAVING to filter groups, or wrap the aggregate in a subquery to compare each row against a single computed value.

Sources

1. SQLite: Built-in Aggregate Functions
2. MySQL 8.4 Reference Manual: Aggregate Function Descriptions
3. MAX (Transact-SQL) - SQL Server
4. PostgreSQL Documentation: SELECT
5. MySQL 8.4 Reference Manual: MySQL Handling of GROUP BY
6. PostgreSQL Documentation: Aggregate Functions