Back to all posts

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) and MAX(column) each take one column or expression.
  • FROM table names the table to read.
  • WHERE condition is optional. WHERE chooses the rows that MIN and MAX read.
  • GROUP BY column is 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_salarymax_salary
40006000

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_hirelast_hirefirst_namelast_name
2019-07-152023-02-01AnnaEva

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;
deptmin_salarymax_salary
HR40005000
IT40006000
SalesNULLNULL

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.

The five employees split by GROUP BY dept into HR, IT and Sales; MIN and MAX read each group's salaries, so HR returns 4000 and 5000, IT 4000 and 6000, and Sales, whose only salary is NULL, returns NULL twice

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;
deptmax_salary
IT6000

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;
namedeptsalary
AnnaIT4000
DoraHR4000

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;
namedeptsalarydept_max
CarlHR50005000
DoraHR40005000
AnnaIT40006000
BenIT60006000
EvaSalesNULLNULL

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);
EngineError
PostgreSQL 18aggregate functions are not allowed in WHERE
MySQL 9.1Invalid use of group function
SQLite 3.50misuse 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;
EngineCollationfirst_name
PostgreSQL 18en_US.utf8adam
MySQL 9.1utf8mb4_0900_ai_ciadam
SQLite 3.50BINARYAnna

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.

The GROUP BY query in the DbSchema SQL Editor, with the minimum and maximum salary of HR, IT and Sales in the result grid under the editor

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.