How to Use MAX and MIN in SQL

Use MAX() and MIN() when you need the highest or lowest value in a column. These functions are perfect for price ranges, earliest dates, latest activity, top scores, and threshold checks.

What you’ll build or solve

You’ll learn how to use MAX and MIN in SQL for numbers, dates, and grouped extremes. You’ll also know how NULL values affect the result.

When this approach works best

This approach is the right choice when only the highest or lowest matching value matters.

Common real-world scenarios include:

• Highest price
• Lowest inventory
• Latest login
• Earliest order
• Best exam score

This is a bad idea when you need the full top row details. In that case, ORDER BY ... LIMIT 1 may be better.

Prerequisites

You only need:

• A SQL table with sortable numeric or date columns
• Basic SELECT, WHERE, and GROUP BY knowledge

Step-by-step instructions

Step 1: Wrap the column in MAX() or MIN()

Use MAX() for the largest value.

SELECT MAX(price)
FROM products;

Use MIN() for the smallest value.

SELECT MIN(price)
FROM products;

This works especially well with dates.

SELECT MAX(last_login)
FROM users;

For grouped extremes, combine with GROUP BY.

SELECT country, MAX(total)
FROM orders
GROUP BY country;

What to look for:

• MAX() returns the highest value
• MIN() returns the lowest value
• Great for numbers and dates
• GROUP BY creates per-group extremes
• NULL values are ignored

Examples you can copy

Highest order

SELECT MAX(total)
FROM orders;

Earliest signup

SELECT MIN(created_at)
FROM users;

Highest score by class

SELECT class_id, MAX(score)
FROM exams
GROUP BY class_id;

Common mistakes and how to fix them

Mistake 1: Expecting the full row

What the reader might do:

SELECT MAX(total)
FROM orders;

Why it breaks: this returns only the highest value, not the entire order row.

Corrected approach:

Use ORDER BY total DESC LIMIT 1 when the full row is needed.

Mistake 2: Forgetting GROUP BY

What the reader might do:

SELECT country, MAX(total)
FROM orders;

Why it breaks: SQL needs grouping for non-aggregated columns.

Corrected approach:

GROUP BY country

Mistake 3: Misreading ignored NULL values

What the reader might do:

SELECT MIN(discount)
FROM orders;

Why it breaks: NULL discounts are skipped.

Corrected approach:

Use COALESCE() if missing values should count as zero.

Troubleshooting

If the result is only one number, that is expected aggregate behavior.

If grouped extremes error, add GROUP BY.

If the result is NULL, confirm matching rows exist.

If the full row is needed, switch to ORDER BY.

Quick recap

• Use MAX() for the highest value
• Use MIN() for the lowest value
• Great for dates and numbers
• Add GROUP BY for grouped extremes
• Use ORDER BY for full top rows