MAX

MAX: The Largest Value

MAX(column) is the mirror image of MIN — it returns the largest non-NULL value in a column, and works on numbers, dates, times, and strings.

sql
SELECT MAX(amount) FROM orders;

With amounts 250.00, NULL, 400.00, 150.00, 300.00:

MAX(amount)
------------
     400.00

MAX on Dates

sql
SELECT MAX(order_date) FROM orders;
-- 2024-03-01   (the most recent order)

Common real-world uses: "what's the most recent login?", "what's the latest invoice date?", "when did this customer last order?".

MAX on Strings

sql
SELECT MAX(customer_name) FROM orders;
-- 'Charlie'   (alphabetically last among Alice, Bob, Alice, Charlie, Bob)

MAX and MIN Together

A very common pattern is fetching both bounds of a range in one query:

sql
SELECT MIN(amount) AS cheapest, MAX(amount) AS priciest FROM orders;
-- cheapest = 150.00, priciest = 400.00

SELECT MIN(order_date) AS first_order, MAX(order_date) AS last_order FROM orders;
-- first_order = 2024-01-05, last_order = 2024-03-01

This is often used to compute a range/spread:

sql
SELECT MAX(amount) - MIN(amount) AS amount_range FROM orders;
-- 400.00 - 150.00 = 250.00

MAX with a Filter

sql
SELECT MAX(amount) FROM orders WHERE status = 'completed';
-- MAX(250.00, 400.00, 300.00) = 400.00

Edge Cases

• MAX() over an empty result set (or an all-NULL column) returns NULL, never an error and never a sentinel like 0 or -1.
• Like MIN, MAX(DISTINCT column) always equals MAX(column) — removing duplicates never changes the largest value.
• Comparing MAX on mixed-precision numeric types (e.g. comparing a FLOAT column with values very close together) can be subject to floating-point representation quirks; using DECIMAL for money (as in this chapter's orders.amount) avoids that entirely — this is why financial data should virtually never be stored as FLOAT.

Key Takeaways / Interview Q&A

Q: What does MAX() return on an empty set? A: NULL — same as MIN, SUM, and AVG (only COUNT returns 0 instead of NULL for an empty set).

Q: Can MAX() find the "most recent" date? A: Yes — since later dates are "larger" in chronological ordering, MAX(date_column) returns the most recent date.

Q: How would you compute the range (spread) between the highest and lowest order amount in one query? A: SELECT MAX(amount) - MIN(amount) FROM orders;

Q: Does DISTINCT ever change the result of MAX or MIN? A: No — MAX(DISTINCT x) and MIN(DISTINCT x) are always identical to MAX(x) and MIN(x) respectively, since deduplication cannot change which value is largest or smallest.