Lesson 22 of 61

5 mins read

SQL MIN() & MAX(): Finding Extremes Across Types

In Plain English

`MIN()` returns the lowest value and `MAX()` returns the highest value in a column or expression. They operate seamlessly across numerical amounts, alphabetical text strings (A to Z), dates, and timestamps.

The Mental Model

"`MIN()` is finding the shortest person or earliest timestamp in a room; `MAX()` is finding the tallest person or most recent event."

Deep Dive: How It Works

Type Support: Numerical (lowest/highest numbers), Date/Time (`MIN(created_at)` = oldest record, `MAX(created_at)` = newest record), Text (`MIN(name)` = alphabetically first name).

Index Optimization: If the column is indexed with a B-Tree, `SELECT MIN(col)` and `SELECT MAX(col)` execute in O(1) instantaneous time by jumping directly to the left-most or right-most leaf node.

Subquery Pattern: Finding the full row holding the max value: `SELECT * FROM products WHERE price = (SELECT MAX(price) FROM products)`.

Syntax Blueprint

SELECT MIN(price) AS cheapest, MAX(price) AS most_expensive
FROM products;

-- Find newest user registration:
SELECT MAX(registered_at) AS latest_signup FROM users;

Use MIN and MAX to discover lowest and highest values across data types.

Core Rules to Remember

Date Extremes: MIN finds oldest/earliest dates; MAX finds newest/latest dates.

O(1) B-Tree Speed: MIN and MAX on indexed columns require zero table scanning.

Full Row Extrema: Use subqueries or window functions to retrieve other columns alongside the maximum.

Common Beginner Traps & How to Fix Them

Mistake:Writing `SELECT name, MAX(price) FROM products` expecting the name belonging to the highest price product.

Why it happens: Without subqueries or GROUP BY, SQL returns the max price paired with an arbitrary name.

How to fix: Use `WHERE price = (SELECT MAX(price) FROM products)` or `ORDER BY price DESC LIMIT 1`.

Pro Tip: In PostgreSQL, use `LEAST(a, b)` and `GREATEST(a, b)` to find the min/max across columns in the SAME row.

Live Interactive Example

Hit Run Code to see it live

Finding Price and Date Extremes

Python 3.12

Output Console

Click "Run Code" to view the rendered output.

How it works: MIN and MAX found the salary extremes ($45k/$110k) and date boundaries.

Your Turn: Micro Challenge

No pressure! Edit the starter code below and test your solution with instant feedback.

Micro Exercise

Query Min and Max Prices

Select `MIN(price) AS min_p`, `MAX(price) AS max_p` from `catalog`.

Sandbox Output

Click "Run & Check" to test your solution.

Finished reading and practicing?

Mark this lesson as completed to update your course progress.