MIN and MAX for extremes

Concepts covered: Min & Max

MIN returns the smallest value in a group and MAX the largest, skipping NULL like every other aggregate. They work on any type that has an ordering: numbers compare numerically, text compares alphabetically under the column's collation, and dates compare chronologically, so MIN(hire_date) is the earliest hire and MAX(hire_date) the most recent. Both return 1 value per group and nothing about the row it came from.

The cheapest product in each category

The products table below lists 6 products, 3 in Electronics priced 299, 149 and 599, and 3 in Clothing priced 45, 89 and 29. Grouping by category and taking MIN(price) gives the floor of each category's range.

Electronics bottoms out at 149 and Clothing at 29. The engine scans each group once, carrying the smallest value seen so far and replacing it whenever a smaller one appears, so MIN costs 1 comparison per row and no sorting. Swapping MIN for MAX in the same query gives the ceiling instead, 599 for Electronics and 89 for Clothing.

The 2 run together in 1 query without conflict, MIN(price) and MAX(price) side by side, and the gap between them is the category's price range. Electronics spans 149 to 599, a range of 450; Clothing spans 29 to 89, a range of 60. MAX(price) - MIN(price) AS price_range is legal because both sides are aggregates, so the expression has exactly 1 value per group.

Dates and text have extremes too

A launched date on each product turns MIN and MAX into first and latest: MIN(launched) per category is the date the category started selling, and MAX(launched) is its newest product. Text works the same way through alphabetical order, which is useful for a stable pick (MIN(product_id) always returns the same id for a group) and otherwise rarely what a report wants. Run the query below, then replace price with launched in the first 2 aggregates and delete the range line to see the dates line up.

SELECT category,
  MIN(price) AS cheapest,
  MAX(price) AS priciest,
  MAX(price) - MIN(price) AS price_range
FROM products
GROUP BY category

With launched in place, Electronics runs from 2022-11-20 to 2023-08-01 and Clothing from 2022-06-30 to 2024-01-15. Dates stored as text in YYYY-MM-DD form sort correctly as text, which is why that format is the convention; a date stored as 15/01/2024 sorts by day first and MIN returns nonsense.

MAX gives the value, never the row

The most common mistake with these 2 functions is asking them a question they cannot answer. MAX(price) for Electronics is 599. Which product is priced at 599? The query has grouped 3 rows into 1 and the product id went the same way sale_id went in the first section: it is neither grouped nor aggregated, so SELECT category, product_id, MAX(price) FROM products GROUP BY category is an error on standard engines and silently wrong on lenient ones. Getting the row that holds the maximum needs a second step, either a join back to the table on category and price or a window function, both of which belong to later lessons.

A report should show, for each category, the name of its most expensive product. Which statement about MAX is correct?

• MAX(price) returns the highest price per category, and a separate step is needed to find which product carries it.
• SELECT category, product_name, MAX(price) FROM products GROUP BY category returns the product name beside the highest price.
• MAX(product_name) returns the name of the most expensive product.
• Adding ORDER BY price DESC makes MAX remember the top row so its name can be selected.

MIN and MAX on a 0/1 flag column are a quick way to ask whether any row in a group is flagged (MAX(is_flagged) = 1) or whether all of them are (MIN(is_flagged) = 1).

About this lesson

This section is part of the Aggregating: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.