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 give back one result. MIN and MAX work on numbers, dates and text, skip NULL values, and combine with WHERE to find extremes in a filtered set or with GROUP BY to find extremes per group.

This chapter is for beginners and for spreadsheet users moving into SQL. You will get the syntax, a small sample table, worked examples with the exact output, practical variations (HAVING, conditional MIN/MAX, window functions), common mistakes and a short practice exercise. Every example runs in MySQL, SQL Server, PostgreSQL and SQLite unless noted.

SQL MIN and MAX Syntax

SELECT MIN(column_name) AS alias_name
FROM table_name
WHERE condition;

SELECT MAX(column_name) AS alias_name
FROM table_name
WHERE condition;

Sample Table

All examples below use this Orders 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. You can also calculate the spread in the same query: MAX(Amount) - MIN(Amount) returns 1175.

Example 2: MIN and MAX on Dates

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

Dates compare chronologically, so MIN is the earliest date and MAX the most recent. This only works reliably when the column has a real DATE or DATETIME type.

Example 3: MIN and MAX on Text

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

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

“Mouse” beats “Monitor” because the third letter, u, comes after n.

Example 4: MAX with a WHERE Filter

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

WHERE runs first, removing the two laptop orders, and MAX is calculated over the remaining four rows (25, 300, 45 and 80). Filtering itself is covered in the WHERE clause chapter of this course.

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, which picks just one. See SQL Subqueries for more patterns like this.

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 SQL GROUP BY chapter.

More Variations You Will Use

Filter groups by their maximum with HAVING

WHERE cannot reference an aggregate, so use HAVING to keep only customers whose largest order is above 500.

SELECT CustomerID, MAX(Amount) AS Largest
FROM Orders
GROUP BY CustomerID
HAVING MAX(Amount) > 500;

The SQL HAVING clause chapter explains the difference from WHERE in detail.

Conditional MIN and MAX with CASE

Wrap a CASE expression inside the aggregate to get several filtered extremes in one pass. Rows that do not match return NULL and are ignored.

SELECT
  MIN(CASE WHEN Product = 'Laptop' THEN Amount END) AS CheapestLaptop,
  MAX(CASE WHEN Product = 'Laptop' THEN Amount END) AS DearestLaptop
FROM Orders;

Keep every row with a window function

Adding OVER turns MAX into a window function, so each row keeps its detail and also shows its group’s maximum. This works in MySQL 8+, SQL Server, PostgreSQL and SQLite 3.25+.

SELECT OrderID, CustomerID, Amount,
       MAX(Amount) OVER (PARTITION BY CustomerID) AS CustomerMax
FROM Orders
ORDER BY OrderID;

Ranking and running totals are covered in the window functions chapter later in the course.

Compare across columns with GREATEST and LEAST

MIN and MAX work down a column. To compare values across columns in the same row, PostgreSQL, MySQL and SQL Server 2022+ offer GREATEST() and LEAST(), for example GREATEST(Q1Sales, Q2Sales, Q3Sales). In SQLite, the multi-argument forms MAX(a, b, c) and MIN(a, b, c) do the same job.

How It Behaves Across Databases

Practical Uses of MIN and MAX

• Last purchase date: MAX(OrderDate) per customer for churn or re-engagement reports.
• First purchase date: MIN(OrderDate) per customer for cohort analysis.
• Data-quality checks: MIN and MAX on numeric columns quickly expose negative amounts or dates in the future.
• Column sizing: MAX(LENGTH(Product)) shows whether text is close to overflowing a column width (LEN() in SQL Server).
• Partially filled columns: because NULLs are skipped, MAX(ShipDate) returns the latest shipment even when many orders have not shipped.

Common Mistakes and Fixes

Tips

• Always alias aggregate columns; it makes results readable and easier to reference in applications.
• An index on the column lets most databases answer a plain MIN or MAX almost instantly, even on large tables.
• When you need the row, not just the value, reach for a subquery or ROW_NUMBER() rather than LIMIT 1 if ties matter.
• Spreadsheet users: SQL MIN and MAX behave like Excel’s MIN and MAX, while GROUP BY with MAX is closer to MAXIFS.

Practice Exercise

1. Find the earliest OrderDate for customer 1.
2. Return the Product of the cheapest order using a subquery.
3. List each customer’s largest order, keeping only customers whose largest order is under 100.

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

-- 3
SELECT CustomerID, MAX(Amount) AS Largest
FROM Orders
GROUP BY CustomerID
HAVING MAX(Amount) < 100;
-- 2 | 25,  3 | 45,  6 | 80

Test Yourself with the Free SQL Quiz

Spreadsheet users can pair this with the Excel AI Mastery: 100 Formulas course on NextGenTemplates Academy, which covers MIN, MAX and the conditional variants with practice files.

Related Chapters

• SQL COUNT, AVG and SUM – the other core aggregate functions
• SQL GROUP BY – MIN and MAX per category
• SQL from Scratch: full course overview – every chapter in order, including WHERE, NULL values, LIMIT and window functions

Frequently Asked Questions

Do MIN and MAX ignore NULL values?

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

Can I use MIN and MAX on text columns?

Yes. They compare alphabetically according to the column’s collation and return 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.

Can I use MIN and MAX in the same query?

Yes. You can put MIN, MAX, COUNT, SUM and AVG side by side in one SELECT, as in Example 1.

What is the difference between MAX and ORDER BY … LIMIT 1?

MAX returns only the value and handles ties naturally when used in a subquery. ORDER BY with LIMIT 1 returns one whole row, but if two rows tie it picks one of them without telling you.

Why does MAX return the wrong number?

Usually the column is stored as text, so values are compared alphabetically. Convert the column to a numeric type or CAST it inside MAX.

Wrapping Up

MIN and MAX are the simplest aggregate functions, but combined with WHERE, GROUP BY, HAVING, CASE and window functions they answer a large share of everyday reporting questions. Try the practice exercise on your own table, then move on to COUNT, AVG and SUM to complete the aggregate toolkit.

📅 Last updated: October 2026

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