AI Hints
Homeโ€บSQL Tutorialโ€บCounting and Totals
SQL ยท Lesson 33 of 126

Smallest and Largest

MIN and MAX.

โฑ 2 min readโ–ถ Video at 46:38๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

What's the cheapest book? What's the most expensive? When did the first customer join? These are all questions about the smallest, or the largest value in a column.

Smallest and Largest
From the video: Smallest and Largest
  • Cheapest and dearest book
  • First and newest customer
  • Smallest and largest

MIN and MAX

Min gives the smallest value in a column, and max gives the largest. You can ask for both in the same query. Run it. The cheapest book is eight ninety nine, and the most expensive is thirty four ninety nine.

query.sqlsql
SELECT MIN(price) AS cheapest,
       MAX(price) AS dearest
FROM books;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ cheapest โ”‚ dearest โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ 8.99     โ”‚ 34.99   โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

They work on dates too

Min and max also work on dates, and even on text. On dates, min means the earliest, and max means the latest. Run it. The first customer joined in January twenty twenty three, and the newest in June twenty twenty five.

query.sqlsql
SELECT MIN(joined) AS first_customer,
       MAX(joined) AS newest_customer
FROM customers;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ first_customer โ”‚ newest_customer โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ 2023-01-04     โ”‚ 2025-06-19      โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Aggregates with WHERE

Aggregates work happily with where. The where runs first, keeping only fantasy books. Then the count and sum work on what's left. Run it. Three fantasy titles, with eighty five copies on the shelves.

query.sqlsql
SELECT COUNT(*)   AS books,
       SUM(stock) AS copies
FROM books
WHERE genre = 'Fantasy';
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ books โ”‚ copies โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ 3     โ”‚ 85     โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Summary

Min, max, count, sum, and average. Those five aggregate functions answer a huge number of business questions. Let's practise them.

Key takeaways
  • MIN() smallest
  • MAX() largest
  • WHERE runs first
Next up: Practice: The Staff Numbers โ€” five aggregates together