SQL MIN() and MAX() Functions Explained with Examples
For SQL beginners who write SELECT queries and now need the smallest or largest value in a column.
On this page
MIN returns the smallest value in a column, and MAX returns the largest. For example, one query returns the lowest and the highest salary in an employees table, or in each department.
Both functions have the same shape:
SELECT MIN(column), MAX(column)
FROM table
WHERE condition
GROUP BY column;
MIN(column)andMAX(column)each take one column or expression.FROM tablenames the table to read.WHERE conditionis optional. WHERE chooses the rows that MIN and MAX read.GROUP BY columnis optional. Without GROUP BY, the query returns one row for the whole table. With GROUP BY, the query returns one row per group.
Create a sample table of five employees and fill it:
CREATE TABLE employees (
name varchar(20),
dept varchar(10),
salary int,
hire_date date
);
INSERT INTO employees VALUES
('Anna', 'IT', 4000, '2021-03-01'),
('Ben', 'IT', 6000, '2019-07-15'),
('Carl', 'HR', 5000, '2022-01-10'),
('Dora', 'HR', 4000, '2020-05-20'),
('Eva', 'Sales', NULL, '2023-02-01');
Every statement below was run on PostgreSQL 18, MySQL 9.1 and SQLite 3.50. All three engines returned the same rows unless an engine is named.
MIN and MAX
MIN and MAX read the column in every row that the query selects, and return one row. For example, you can get the lowest and the highest salary of all employees.
SELECT MIN(salary) AS min_salary,
MAX(salary) AS max_salary
FROM employees;
| min_salary | max_salary |
|---|---|
| 4000 | 6000 |
Anna and Dora both earn the lowest salary, 4000, and Ben earns the highest, 6000. The result shows only the two values, not whose salaries they are.
Eva's salary is NULL, which marks a missing value. Both functions skip NULL rather than reading it as 0.
AS names each result column. Without AS, MySQL and SQLite call the column MIN(salary), and PostgreSQL calls it min.
Practice this in Lesson 6 of the SQL tutorial: you run COUNT, SUM, AVG, MIN and MAX on a shop database in your browser, with checked exercises.
MIN and MAX on dates and text
MIN and MAX work on any column whose values can be sorted, such as dates and text. For example, MIN on a date column returns the earliest date, and MAX the latest.
SELECT MIN(hire_date) AS first_hire,
MAX(hire_date) AS last_hire,
MIN(name) AS first_name,
MAX(name) AS last_name
FROM employees;
| first_hire | last_hire | first_name | last_name |
|---|---|---|---|
| 2019-07-15 | 2023-02-01 | Anna | Eva |
Ben was hired first, in July 2019, and Eva last, in February 2023. On the name column, MIN returns Anna, the name that sorts first, and MAX returns Eva, the name that sorts last.
The order of text depends on the column's collation, its rules for comparing text. For a name that the engines sort differently, see "Text order depends on the collation" below.
MIN and MAX with GROUP BY
GROUP BY splits the rows into groups, and MIN and MAX run once in each group. For example, you can get the lowest and the highest salary of each department.
SELECT dept,
MIN(salary) AS min_salary,
MAX(salary) AS max_salary
FROM employees
GROUP BY dept
ORDER BY dept;
| dept | min_salary | max_salary |
|---|---|---|
| HR | 4000 | 5000 |
| IT | 4000 | 6000 |
| Sales | NULL | NULL |
HR holds Carl and Dora, so its salaries run from 4000 to 5000. IT runs from Anna's 4000 to Ben's 6000. Sales holds only Eva, whose salary is NULL, so both functions return NULL for Sales.
MIN and MAX return NULL for a group only when every value in that group is NULL.
MIN and MAX in HAVING
After MIN and MAX have run, HAVING keeps only the groups that match a condition. For example, you can keep the departments whose highest salary is above 5000.
SELECT dept, MAX(salary) AS max_salary
FROM employees
GROUP BY dept
HAVING MAX(salary) > 5000;
| dept | max_salary |
|---|---|
| IT | 6000 |
IT's maximum is 6000, so IT stays. HR's maximum is 5000, which is not above 5000, so HR is dropped. Sales has a NULL maximum, and a comparison with NULL is never true, so Sales is dropped too.
In HAVING, write MAX(salary) again rather than the alias max_salary, the name that AS gave the column. PostgreSQL 18 rejects the alias with "column "max_salary" does not exist". MySQL documents the alias in HAVING as an extension to standard SQL, and SQLite also accepts the alias.
MIN in a subquery
To get the row that holds the lowest salary, use a subquery: a SELECT inside another query. Here, the subquery in parentheses computes the lowest salary, and the outer query returns the employees with that salary.
SELECT name, dept, salary
FROM employees
WHERE salary = (SELECT MIN(salary) FROM employees)
ORDER BY name;
| name | dept | salary |
|---|---|---|
| Anna | IT | 4000 |
| Dora | HR | 4000 |
The subquery returns 4000. Anna and Dora tie on 4000, so both come back. With MAX in place of MIN, the query returns Ben.
MAX with OVER
With OVER, MAX keeps every row and adds the maximum in one more column. For example, with PARTITION BY dept, each row shows the highest salary of its department.
SELECT name, dept, salary,
MAX(salary) OVER (PARTITION BY dept) AS dept_max
FROM employees
ORDER BY dept, name;
| name | dept | salary | dept_max |
|---|---|---|---|
| Carl | HR | 5000 | 5000 |
| Dora | HR | 4000 | 5000 |
| Anna | IT | 4000 | 6000 |
| Ben | IT | 6000 | 6000 |
| Eva | Sales | NULL | NULL |
Carl and Dora both show HR's maximum, 5000, and Anna and Ben show IT's, 6000. Each row keeps its own salary beside its department's maximum.
To get the top earner of each department, keep the rows where salary equals dept_max in an outer query. That outer query returns Carl and Ben. For OVER and the outer query, see Window functions in SQL.
What goes wrong
MIN or MAX in WHERE
WHERE can't use MIN or MAX, because WHERE runs before MIN and MAX have computed their value. This query fails on all three engines:
SELECT name, salary
FROM employees
WHERE salary = MAX(salary);
| Engine | Error |
|---|---|
| PostgreSQL 18 | aggregate functions are not allowed in WHERE |
| MySQL 9.1 | Invalid use of group function |
| SQLite 3.50 | misuse of aggregate function MAX() |
To compare each row with the maximum, use a subquery, as in "MIN in a subquery". To filter groups, use HAVING.
A column next to MIN or MAX
Without GROUP BY, MAX returns one row, but the name column holds five names. This query has no single name to show:
SELECT name, MAX(salary)
FROM employees;
PostgreSQL 18 rejects it with "column "employees.name" must appear in the GROUP BY clause or be used in an aggregate function". MySQL 9.1 rejects it too, because its ONLY_FULL_GROUP_BY mode is on by default.
SQLite runs the query and returns Ben and 6000. With one MIN or MAX in the query, SQLite takes the other columns from a row that holds the MIN or MAX value.
Only SQLite accepts a column beside MAX without GROUP BY. On other engines, use the subquery from "MIN in a subquery".
MAX on no rows returns NULL
No employee works in Finance, so MAX has no salary to read:
SELECT MAX(salary) AS max_salary
FROM employees
WHERE dept = 'Finance';
| max_salary |
|---|
| NULL |
An aggregate function, such as MIN or MAX, computes one value from many rows. PostgreSQL documents that every aggregate function except count returns NULL when no rows are selected.
If your code expects a number here, wrap MAX in COALESCE. COALESCE(MAX(salary), 0) returns 0 when MAX returns NULL:
SELECT COALESCE(MAX(salary), 0) AS max_salary
FROM employees
WHERE dept = 'Finance';
| max_salary |
|---|
| 0 |
ORDER BY with LIMIT 1 in place of MIN
Sorting by salary and keeping the first row with LIMIT 1 looks like MIN, but ORDER BY doesn't skip NULL:
SELECT name, salary
FROM employees
ORDER BY salary
LIMIT 1;
PostgreSQL 18 returns Anna with 4000, because PostgreSQL sorts NULL after every other value. MySQL 9.1 and SQLite 3.50 sort NULL first, so they return Eva with NULL.
Add WHERE salary IS NOT NULL, and all three return Anna. LIMIT 1 still keeps only one row of a tie. The subquery in "MIN in a subquery" returned both Anna and Dora.
Text order depends on the collation
Add one row whose name starts with a lowercase letter, and the engines disagree:
INSERT INTO employees VALUES ('adam', 'IT', 5500, '2024-04-01');
SELECT MIN(name) AS first_name
FROM employees;
| Engine | Collation | first_name |
|---|---|---|
| PostgreSQL 18 | en_US.utf8 | adam |
| MySQL 9.1 | utf8mb4_0900_ai_ci | adam |
| SQLite 3.50 | BINARY | Anna |
The collations of PostgreSQL and MySQL compare the letters and ignore case at first, so adam comes before Anna.
SQLite's default collation, BINARY, compares the bytes of the text. In bytes, every uppercase letter comes before every lowercase one. PostgreSQL's C collation compares bytes too, so MIN on a column declared with C returns Anna.
Run the queries in DbSchema
The SQL Editor in DbSchema runs a query and shows its rows in a grid under the editor. Press Editor in the toolbar, choose SQL Editor, paste a query from this article, and press Run.
Download DbSchema at https://dbschema.com/download.html and connect it to PostgreSQL, MySQL or SQLite. Paste the CREATE TABLE and INSERT statements into the SQL Editor, then run and change each query. Connecting to a database and the SQL Editor are part of the free Community Edition.
Run your MIN() and MAX() queries against the real schema
DbSchema connects to your database, runs SQL beside the result grid, and draws the tables you are aggregating as an interactive diagram. The SQL editor and the diagrams are in the free Community Edition.