MIN() & MAX()
In this page:
Finding Minimum Values
The MIN() function scans a column and returns the lowest value found among the matched rows. It is ideal for finding cheapest prices, oldest dates, or lowest scores without manually sorting and picking the first row.
Example: Finding Minimum Values
CREATE TABLE products (id INT, price INT);
INSERT INTO products VALUES (1, 50), (2, 10), (3, 75);
SELECT MIN(price) AS cheapest FROM products;Finding Maximum Values
The MAX() function scans a column and returns the highest value found among the matched rows. This is useful for finding peak sales, top scores, or newest items, especially when paired with a date column.
Example: Finding Maximum Values
CREATE TABLE orders (id INT, order_date TEXT, total INT);
INSERT INTO orders VALUES (1, '2024-01-01', 50), (2, '2024-06-01', 200);
SELECT MAX(total) AS peak_sale FROM orders;Using MIN() and MAX() Together
You can call both MIN() and MAX() in a single query to see both ends of a range at once. This lets you view the entire range of values in your dataset at once, like the earliest and latest order dates side by side.
Example: Using MIN() and MAX() Together
CREATE TABLE orders (id INT, order_date TEXT);
INSERT INTO orders VALUES (1, '2024-01-05'), (2, '2024-06-20'), (3, '2024-03-10');
SELECT MIN(order_date) AS earliest, MAX(order_date) AS latest FROM orders;Non-Numeric MIN() and MAX()
MIN() and MAX() are not limited to numbers and work on text and date columns too. They can find alphabetical boundaries on text columns and temporal boundaries on date columns, using the column's own natural ordering.
Example: Non-Numeric MIN() and MAX()
CREATE TABLE products (id INT, name TEXT);
INSERT INTO products VALUES (1, 'Zebra Print'), (2, 'Apple Case');
SELECT MIN(name) AS first_alphabetically, MAX(name) AS last_alphabetically FROM products;Aggregating Ranges inside GROUP BY
You can combine MIN() and MAX() with a GROUP BY clause to find boundaries for separate categories, such as the highest and lowest price within each product category rather than across the whole catalog.
Example: Aggregating Ranges inside GROUP BY
CREATE TABLE products (id INT, category TEXT, price INT);
INSERT INTO products VALUES (1, 'Books', 10), (2, 'Books', 40), (3, 'Toys', 20);
SELECT category, MIN(price) AS lowest, MAX(price) AS highest FROM products GROUP BY category;Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: