DocsAggregationsMIN & MAX

MIN & MAX

Aggregations

Concept

MIN returns the smallest value and MAX returns the largest value in a group. These functions work on numbers, strings (alphabetical comparison), dates, and any other sortable type. They are indispensable for finding extremes: the cheapest product, the latest order date, the first customer signup.

Like other aggregate functions, MIN and MAX ignore NULL values. If every value in the group is NULL, they return NULL. They can be used with or without GROUP BY — without it, they scan the entire table and return a single row.

MIN and MAX are often efficient because the database can use an index on the target column to find the answer without scanning every row. This makes queries like "what is the most recent order date?" very fast when order_date is indexed.

Syntax

-- Global min and max
SELECT
  MIN(price) AS cheapest,
  MAX(price) AS most_expensive
FROM products;

-- Per-group min and max
SELECT
  category,
  MIN(price) AS lowest_price,
  MAX(price) AS highest_price
FROM products
GROUP BY category;

-- With dates
SELECT
  MIN(order_date) AS first_order,
  MAX(order_date) AS latest_order
FROM orders;

Practical Example

Using the ecommerce schema (customers, orders, order_items, products)

-- Price range and count per category
SELECT
  category,
  COUNT(*)     AS product_count,
  MIN(price)   AS min_price,
  MAX(price)   AS max_price,
  MAX(price) - MIN(price) AS price_spread
FROM products
GROUP BY category
ORDER BY price_spread DESC;

-- Each customer's first and most recent order
SELECT
  c.customer_id,
  c.first_name || ' ' || c.last_name AS customer,
  MIN(o.order_date) AS first_order,
  MAX(o.order_date) AS latest_order,
  MAX(o.order_date) - MIN(o.order_date) AS customer_lifetime
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
ORDER BY customer_lifetime DESC;

Common Pitfalls & Tips

1MIN/MAX on strings use alphabetical order, which may not match your expectations for mixed-case or locale-sensitive data.
2If you want the entire row with the minimum or maximum value (not just the value itself), you need a subquery or window function — not just MIN/MAX in SELECT.
3MIN and MAX return NULL if the group is empty. Guard against this with COALESCE when feeding results into further calculations.

Practice MIN & MAX queries