Skip to content
SQLSimplified
aggregate

MIN() and MAX() Across Groups

Find the cheapest and most expensive product within each category.

Overview

MIN() and MAX() return the smallest and largest value of a column. With GROUP BY they reveal the range inside each bucket: here, the lowest and highest priced product per category in the store.

The Query

Loading playground environment...

Step-by-Step Breakdown

  1. MIN(price) / MAX(price): the category's price floor and ceiling.
  2. MAX(price) - MIN(price): a derived price_spread column.
  3. GROUP BY category: ranges computed per category, not globally.

MIN/MAX on dates and text

These also work on dates (earliest/latest) and text (alphabetical first/last), not just numbers.

Variations

Find earliest and latest movie release per genre:

Loading playground environment...

Common Mistakes

  • Confusing with LIMIT. MIN(price) returns the value; ORDER BY price LIMIT 1 returns the row. Use the aggregate when you only need the number.
  • NULL handling. MIN/MAX ignore NULLs, which can hide missing data.
  • Non-grouped column in SELECT. SELECT name, MIN(price) without grouping name is invalid.

Related examples

Core Aggregate Functions: COUNT, SUM, AVG, MIN, MAX
aggregate
A single query demonstrating all five core aggregates over the employees table.
SUM() Aggregation With GROUP BY
group by
Sum total units ordered per product category using SUM() grouped by category.

Cite this resource

SQLSimplified. "MIN() and MAX() Across Groups Example". Available at: https://sqlsimplified.online/examples/min-max