🎓 A1Learner
🔍

SQL Min and Max

MIN and MAX are aggregate functions that find the smallest and largest values in a column. They work on numbers and on text.

Cheapest and priciest

MIN and MAX read the price column of products. The smallest price is 0.8 and the largest is 59.99.

SELECT MIN(price) AS cheapest_price, MAX(price) AS priciest_price
FROM products;

First and last names

On text, the smallest value is the first in alphabet order. The largest value is the last.

SELECT MIN(name) AS first_name, MAX(name) AS last_name
FROM customers;

Earliest and latest orders

Order dates are stored as text in year-month-day shape. Text order matches date order for that shape, so MIN finds the earliest date.

SELECT MIN(order_date) AS first_order, MAX(order_date) AS last_order
FROM orders;

Example

SELECT MIN(price) AS lowest_price, MAX(price) AS highest_price, MIN(name) AS first_product, MAX(name) AS last_product
FROM products;

Key points

  • MIN: an aggregate function that returns the smallest value in a column
  • MAX: an aggregate function that returns the largest value in a column
  • On numbers, MIN and MAX compare by size.
  • On text, MIN and MAX compare by alphabet order.
  • Both functions skip rows where the column holds NULL.
  • One SELECT can ask for MIN and MAX together.

In this example, one SELECT asks four questions about products. MIN and MAX on price compare the numbers by size. MIN and MAX on name compare the text by alphabet order. All four answers come back in one row.

Try it Yourself

Edit the code, then press Run.

Running is simulated · DB Fiddle

Exercise

What does MAX(name) return from the customers table?