SQL MIN and MAX Functions: Find Smallest and Largest Values

SQL Tutorial · Chapter 15 of 32

The SQL MIN and MAX functions return the smallest and largest value in a column. They are aggregate functions: they read many rows and return one result. MIN and MAX work on numbers, dates and text, ignore NULL values, and combine with WHERE to find extremes within a filtered set or with GROUP BY to find extremes per group.

SQL MIN and MAX Syntax

SELECT MIN(column_name) FROM table_name WHERE condition;
SELECT MAX(column_name) FROM table_name WHERE condition;

Give the result a readable name with an alias, otherwise the heading will be something like MIN(Amount).

Sample Table

Example 1: Smallest and Largest Amount

SELECT MIN(Amount) AS Cheapest, MAX(Amount) AS MostExpensive
FROM Orders;

Six rows go in, one row comes out. That is the defining behaviour of an aggregate function.

Example 2: MIN and MAX on Dates

SELECT MIN(OrderDate) AS FirstOrder, MAX(OrderDate) AS LatestOrder
FROM Orders;

Example 3: MIN and MAX on Text

Text is compared alphabetically, so MIN returns the first name in A-to-Z order and MAX the last.

SELECT MIN(Product) AS FirstAlpha, MAX(Product) AS LastAlpha
FROM Orders;

Example 4: MAX with a WHERE Filter

SELECT MAX(Amount) AS BiggestSmallOrder
FROM Orders
WHERE Product <> 'Laptop';

WHERE runs first, removing the two laptop orders, and MAX is calculated over the remaining four rows.

Example 5: Return the Whole Row of the Maximum

MIN and MAX return a value, not a row. To see which order was the largest, compare against a subquery.

SELECT OrderID, CustomerID, Product, Amount
FROM Orders
WHERE Amount = (SELECT MAX(Amount) FROM Orders);

If several rows tie for the maximum, this query returns all of them, unlike ORDER BY Amount DESC LIMIT 1.

Example 6: MIN and MAX per Group

SELECT CustomerID, MIN(Amount) AS Smallest, MAX(Amount) AS Largest
FROM Orders
GROUP BY CustomerID
ORDER BY CustomerID;

Grouping is covered in depth in the GROUP BY chapter.

Works in MySQL, SQL Server, PostgreSQL, SQLite

• MIN and MAX behave identically in all four and skip NULLs; on an all-NULL or empty set they return NULL.
• Text comparison follows collation, so case handling differs (PostgreSQL and SQLite are case sensitive).
• PostgreSQL and MySQL 8 also offer GREATEST() and LEAST() to compare values across columns in one row; SQL Server 2022 added them too.

Practical Uses of MIN and MAX

Beyond finding the cheapest and dearest order, MIN and MAX solve everyday reporting questions. MAX(OrderDate) per customer gives the last purchase date for a churn report; MIN(OrderDate) gives the acquisition date for cohort analysis. MAX on a status or version column returns the latest version stored as text or number. In data-quality checks, MIN and MAX on every numeric column quickly expose impossible values such as negative amounts or dates in the future, and MAX(LENGTH(Name)) tells you whether a column width is about to overflow.

Because they ignore NULL, MIN and MAX are also safe on partially filled columns: MAX(ShipDate) returns the latest shipment even when many orders have not shipped yet.

Common Mistakes

• Selecting a non-aggregated column next to MAX without GROUP BY: SELECT Product, MAX(Amount) FROM Orders is an error in most databases and returns a random product in MySQL’s permissive mode.
• Expecting MAX on a text column that stores numbers to return the numeric maximum; ’80’ sorts after ‘1200’.
• Using MAX to find the latest row and then assuming other columns in the same SELECT belong to that row.

Practice Exercise

1. Find the earliest OrderDate for customer 1.
2. Return the Product of the cheapest order using a subquery.

-- 1
SELECT MIN(OrderDate) AS FirstOrder FROM Orders WHERE CustomerID = 1;
-- 2026-01-05

-- 2
SELECT Product FROM Orders WHERE Amount = (SELECT MIN(Amount) FROM Orders);
-- Mouse

Related Chapters

• SQL COUNT, AVG, SUM – the other aggregate functions
• SQL GROUP BY – MIN and MAX per category
• SQL Subqueries – returning the row behind a MAX
• SQL from Scratch: full course overview

FAQ

Do MIN and MAX ignore NULL values?

Yes. NULLs are skipped, so MAX returns the largest real value. If every value is NULL, the result is NULL.

Can I use MIN and MAX on text columns?

Yes. They compare alphabetically according to the column’s collation, returning the first and last value in sort order.

How do I get the full row that has the maximum value?

Use a subquery: WHERE Amount = (SELECT MAX(Amount) FROM Orders). It returns every row that ties for the maximum.

Chapter 15 of 32 · SQL from Scratch: all 32 chapters