SR

MIN & MAX

Find the smallest and largest values in a column

Intermediate 8 minMINMAXaggregate

Finding Extremes

MIN returns the smallest value in a column; MAX returns the largest. Both ignore NULLs and work across numbers, text, and dates. Here's what to know:

Works beyond numbers — For text, MIN returns the first value alphabetically (A before Z) and MAX returns the last. For ISO dates (YYYY-MM-DD), MIN gives the earliest date and MAX gives the most recent one.
Range in one expression — MAX(total) - MIN(total) AS price_range computes the full spread of values in a single expression without needing two separate queries or subqueries to find each extreme.
Non-numeric types — Unlike SUM and AVG which require numbers, MIN and MAX work on any comparable type. MAX(name) returns the last name alphabetically, MIN(order_date) returns the very first order.
Knowledge Canvas

How MIN/MAX Work

Find boundary values

  • MIN returns the smallest non-NULL value
  • MAX returns the largest non-NULL value
  • Works on numbers, dates, and strings (alphabetical)
  • Both skip NULLs — all NULLs → returns NULL

MIN/MAX Patterns

Beyond simple boundaries

  • Date ranges: MIN(order_date), MAX(order_date)
  • Price range: MIN(price) || ' – ' || MAX(price)
  • Latest record: WHERE date = (SELECT MAX(date) FROM ...)
  • With GROUP BY: per-group extremes

NULL & Type Behavior

Edge cases to know

  • NULLs are ignored by both MIN and MAX
  • All NULLs → NULL result
  • String MIN/MAX follows collation order
  • MIN('apple','Banana') depends on case sensitivity

MIN/MAX vs Other Approaches

Choosing the right tool

MIN/MAX: single value per group
ORDER BY + LIMIT: full row with the extreme
Returns just the value
Returns the entire row
Can't get "the row with max"
Gets the row naturally
Syntax
Syntax Template
1SELECT
2 MIN(column),
3 MAX(column)
4FROM table;
MIN(column)Smallest non-NULL value
MAX(column)Largest non-NULL value
Sample Data
products
10 rows
nameprice
Wireless Mouse24.99
Office Desk189
LED Desk Lamp45.5
USB-C Cable12.99
Bluetooth Speaker79
Office Chair95
Coffee Maker89.99
Notebook Set8.5
Wireless Headphones159
Standing Desk425
orders
12 rows
order_datetotal
2024-01-1559.98
2024-02-08449
2024-02-2012.5
2024-03-0589.99
2024-03-15245
2024-03-22159
2024-04-0245.5
2024-04-1279
2024-04-22425
2024-05-0518.99
2024-05-18134
2024-06-0865.5

Worked Example

Find the cheapest price, most expensive price, and the price range from the products table.

SQL
1SELECT
2 MIN(price) AS cheapest,
3 MAX(price) AS most_expensive,
4 MAX(price) - MIN(price) AS price_spread
5FROM products;
MIN finds the lowest price (12.50 — Notebook Set). MAX finds the highest (449.00 — Standing Desk). The difference gives the full price spread of 436.50.
Output
1 row
cheapestmost_expensiveprice_spread
8.5425416.5

Key Concepts

1MIN/MAX work on text (alphabetical) and dates — not just numbers
2MAX(date) gives the latest date, MIN(date) the earliest
3MAX(col) - MIN(col) gives the range in one expression
4Both ignore NULLs — they return extremes of non-NULL values

Pro Tip

Extremes reveal data boundaries — cheapest product, latest order, highest spend. Essential for range checks and anomaly detection.

When to Use

Cheapest/most expensive product, earliest/latest order, highest/lowest ratings, date range of a dataset.

Challenge

Solve the problem below

Find the earliest and latest order dates, and the smallest and largest order totals from the orders table. Use aliases: earliest_order, latest_order, min_total, max_total.

Your Query

Previous

AVG Function

Next

GROUP BY