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

SQL MAX() returns the largest value in a column and SQL MIN() returns the smallest, both written as one column name inside the SELECT list: 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].

Table of contents

• The SQL MIN() function
• The SQL MAX() function
• Selecting the full row that holds the MIN or MAX value
• MIN() and MAX() with GROUP BY
• MIN() and MAX() with ORDER BY
• Filtering on MIN() and MAX(): WHERE vs HAVING
• Combining MIN() and MAX() with COUNT, SUM and AVG
• MIN() and MAX() on text and date columns
• Checking MIN() and MAX() results in DbSchema
• Common mistakes with MIN() and MAX()
• FAQs
• Practice questions
• Next steps

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, so the result of one example can be checked against the next.

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. Recovering the row behind the number is a separate query, covered further down.

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(column_name)
FROM table_name;

Against the Employees table above:

SELECT MAX(Salary)
FROM Employees;

The result is again a single row:

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 function accepts more than one column. To compare two columns of the same row you need 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, not the aggregate. 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);

The result carries the identifying columns the aggregate on its own could not:

Swapping MAX for MIN returns John Doe on 50000 instead. Two details decide whether this query is correct for your data:

• Ties return every matching row. If two employees both earn 60000, both come back. That is usually what you want, but it means the query is not guaranteed to return exactly one row.
• The subquery is evaluated over the whole table unless you repeat the outer filter inside it. A WHERE Department = 'IT' on the outer query does not narrow the subquery, so the comparison would be 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. PostgreSQL, MySQL 8 and SQLite from 3.25.0 onwards document the same OVER form.

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 that mode is part of MySQL's own default SQL mode.

Every column in the SELECT list therefore has to be either inside an aggregate or named in the GROUP BY. If you need an ungrouped column next to the aggregate, use the full-row subquery pattern above instead.

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. This is how you get the cheapest or the most expensive group to the top without scanning the output by eye.

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

Ascending order is the default, so the lowest minimum comes first:

Adding DESC flips it, which is the form you want when the question is about the top group:

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

An alias is safe here. 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.

Filtering on MIN() and MAX(): WHERE vs HAVING

WHERE and HAVING 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:

-- invalid: an aggregate cannot appear in 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:

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

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. 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, but its own manual is explicit that this is an extension to standard SQL[5] rather than standard behaviour.

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 are worth reading carefully. 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 both salaries are integers that happen to average cleanly; on real data AVG usually returns a fractional type while MIN and MAX keep the column's own type.

Aliases are what make this output readable. Without AS MinSalary the column comes back labelled MIN(Salary), which is legal but awkward to reference from application code. The same aliasing works on a single aggregate:

SELECT MIN(Salary) AS MinimumSalary,
       MAX(Salary) AS MaximumSalary
FROM Employees;

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 the date columns the reading is unambiguous: the earliest hire is John Doe in March 2019 and the latest is Jane Smith in July 2021. MIN() over a date column is the oldest date and MAX() is the newest, which makes the pair a compact way to state the range a table covers.

On the text column the reading needs a caveat. Jane and Sam come back not because of alphabetical order in the everyday sense but because of the column's collation. 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. Running the statement against the real schema and reading the result next to the SQL that produced it is what turns a silent wrong answer into a visible one, and DbSchema puts both views of the same query on one screen.

Running a MIN() or MAX() query in the SQL editor

Open the SQL editor against the connected database and paste the statement. The editor runs it against the live connection and returns the aggregate row in the result grid, with the execution log underneath showing what was sent and how long it took. Reading the returned row next to the log is how you confirm that a MIN() or MAX() came back from the rows you expected rather than from a filter that silently removed some of them. The SQL editor is part of the free Community Edition.

Building a grouped MIN() or MAX() query visually

Toggling Group By in the visual query builder writes the GROUP BY clause into the generated SQL and turns every ticked column into an aggregate picker, so MIN and MAX per group are chosen from a list instead of typed. The SQL preview updates as you tick, which makes it a fast way to check that the grouping column and the aggregated column are the ones you meant. The visual query builder is a Pro feature.

Either way the generated SQL stays visible, so the query can be copied out into application code once the numbers check out.

Common mistakes with MIN() and MAX()

1. Putting the aggregate in WHERE. MIN() and MAX() are computed after WHERE has already filtered the rows, so the condition has to move to HAVING or into a subquery.
2. Reusing the alias in HAVING. It works on MySQL and fails on PostgreSQL. Repeat the aggregate expression instead.
3. Selecting an ungrouped column beside the aggregate. Every column outside an aggregate has to appear in GROUP BY, or the engine rejects the query rather than guessing which row to print.
4. Expecting zero from an empty result. Aggregates other than COUNT return NULL when no rows qualify, and NULL values propagate through any arithmetic done on them afterwards.
5. Reading MAX() on a text column as alphabetical order. It is collation order, and a case-sensitive or accent-sensitive collation does not agree with the alphabet.
6. Assuming a single row comes back from the full-row subquery. Ties return every matching row.

FAQs

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

Yes. 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?

Yes, on any type with a defined sort order. 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 the collation is case-sensitive or accent-sensitive.

Can MIN() and MAX() be combined with other aggregate functions?

Yes. They sit in the same SELECT list as COUNT, SUM and AVG, and each one is computed across all the rows making up each group. Watch the difference in NULL handling: COUNT(*) counts every row while the value aggregates skip the NULL ones.

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

Yes, and it is the normal way to report a range. SELECT MIN(Salary), MAX(Salary) FROM Employees returns both bounds in a single row and reads the table once.

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

Because 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.

Practice questions

1. Return the highest and the lowest salary in the Employees table in one row, with readable column names.
2. Return one row per department showing headcount, minimum salary and maximum salary.
3. Return only the departments whose maximum salary is above 55000, without using an alias in the condition.
4. Return the full row for the employee hired earliest, and say what your query does if two employees share that hire date.
5. Return the earliest and latest hire date per department, sorted so that the most recently staffed department comes first.

Next steps

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. The rules that keep them correct are short: group every column you did not aggregate, filter groups with HAVING and rows with WHERE, and read a NULL result as no data rather than as zero.

Run these queries against your own schema in DbSchema. Download DbSchema and connect to the database: the SQL editor and the interactive diagrams are in the free Community Edition, and the visual query builder that assembles a grouped MIN or MAX query without typing it is part of DbSchema Pro.

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