Course: MySQL Training
Topic: Finding Minimum and Maximum Values Using MIN() and MAX()
Course Link: MySQL Training Course
Demo Link: Register for a Course Demo
MySQL provides aggregate functions such as MIN() and MAX() to identify the smallest and largest values in a column or expression. These functions are useful when analyzing numeric values, dates, times, and text values.
The MIN() function returns the minimum value from the selected records, while the MAX() function returns the maximum value. Both functions are commonly used in sales reports, employee management systems, product catalogs, inventory databases, financial analysis, and data reporting.
For example, a business may use MIN() to find the lowest product price and MAX() to identify the highest product price. An HR department may use these functions to find the earliest joining date or the latest joining date among employees.
Consider a table named products containing the following records:
| product_id | product_name | price |
|---|---|---|
| 1 | Keyboard | 1500.00 |
| 2 | Mouse | 800.00 |
| 3 | Monitor | 12000.00 |
| 4 | Laptop | 55000.00 |
To find the lowest and highest prices, execute:
SELECT
MIN(price) AS minimum_price,
MAX(price) AS maximum_price
FROM products;Expected output:
| minimum_price | maximum_price |
|---|---|
| 800.00 | 55000.00 |
The minimum price is 800.00, and the maximum price is 55000.00.
After completing this topic, you will be able to:
MIN() and MAX() aggregate functions.MIN() and MAX() in a single SQL query.GROUP BY.HAVING.An aggregate function performs a calculation on a collection of values and returns a summarized result. Instead of returning every individual value, it produces a result based on the selected records.
MySQL supports several aggregate functions, including SUM(), COUNT(), AVG(), MIN(), and MAX().
| Function | Purpose | Example |
|---|---|---|
MIN() |
Returns the smallest value. | MIN(price) |
MAX() |
Returns the largest value. | MAX(price) |
SUM() |
Adds numeric values. | SUM(amount) |
COUNT() |
Counts rows or non-NULL values. | COUNT(*) |
AVG() |
Calculates the average of non-NULL numeric values. | AVG(price) |
MIN() is an aggregate function that returns the smallest non-NULL value from the selected values. It can be used with numeric values, dates, times, and strings.
MAX() is an aggregate function that returns the largest non-NULL value from the selected values. Its result depends on the data type and comparison rules used by MySQL.
To practise these functions, create a database and a sample product table.
CREATE DATABASE company_db;This statement creates a database named company_db. If it already exists, MySQL will report an error unless you use an appropriate existence check.
USE company_db;This statement selects the database for subsequent SQL operations.
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100),
category VARCHAR(50),
price DECIMAL(10, 2),
stock_quantity INT,
created_at DATE
);The table contains product names, categories, prices, inventory quantities, and creation dates. The DECIMAL(10, 2) data type is appropriate for many monetary values because it supports fixed decimal precision.
INSERT INTO products
(product_id, product_name, category, price, stock_quantity, created_at)
VALUES
(1, 'Keyboard', 'Accessories', 1500.00, 25, '2026-01-05'),
(2, 'Mouse', 'Accessories', 800.00, 50, '2026-01-08'),
(3, 'Monitor', 'Electronics', 12000.00, 15, '2026-01-12'),
(4, 'Laptop', 'Electronics', 55000.00, 10, '2026-02-02'),
(5, 'Printer', 'Electronics', 18000.00, 8, '2026-02-10'),
(6, 'Office Chair', 'Furniture', 7500.00, 20, '2026-02-15'),
(7, 'Office Desk', 'Furniture', 15000.00, 12, '2026-03-01'),
(8, 'Webcam', 'Accessories', 3200.00, 30, '2026-03-06');These records provide different prices, stock quantities, categories, and dates for practising minimum and maximum calculations.
SELECT *
FROM products;Viewing the records helps you understand the data before applying aggregate functions.
The MIN() function finds the smallest value among the selected records. It is frequently used to identify the lowest price, smallest quantity, earliest date, or minimum recorded measurement.
SELECT MIN(column_name) AS minimum_value
FROM table_name;Explanation:
SELECT specifies the value to retrieve.MIN() finds the smallest value.column_name identifies the column being evaluated.AS minimum_value assigns a readable name to the result.FROM table_name identifies the source table.SELECT MIN(price) AS lowest_price
FROM products;Expected output:
| lowest_price |
|---|
| 800.00 |
The lowest price is 800.00, corresponding to the Mouse product.
SELECT MIN(stock_quantity) AS minimum_stock
FROM products;Expected output:
| minimum_stock |
|---|
| 8 |
This query returns the smallest stock quantity recorded among all products.
SELECT MIN(created_at) AS earliest_product_date
FROM products;Expected output:
| earliest_product_date |
|---|
| 2026-01-05 |
For date columns, MIN() returns the earliest non-NULL date.
The MAX() function finds the largest value among the selected records. It can be used to identify the highest price, greatest stock quantity, latest date, or maximum recorded measurement.
SELECT MAX(column_name) AS maximum_value
FROM table_name;SELECT MAX(price) AS highest_price
FROM products;Expected output:
| highest_price |
|---|
| 55000.00 |
The highest price is 55000.00, corresponding to the Laptop product.
SELECT MAX(stock_quantity) AS maximum_stock
FROM products;Expected output:
| maximum_stock |
|---|
| 50 |
This query returns the largest stock quantity recorded in the table.
SELECT MAX(created_at) AS latest_product_date
FROM products;Expected output:
| latest_product_date |
|---|
| 2026-03-06 |
For date columns, MAX() returns the latest non-NULL date.
You can use both aggregate functions in a single query to find the lowest and highest values at the same time. This approach is efficient and useful for reporting.
SELECT
MIN(price) AS lowest_price,
MAX(price) AS highest_price
FROM products;Expected output:
| lowest_price | highest_price |
|---|---|
| 800.00 | 55000.00 |
SELECT
MIN(stock_quantity) AS minimum_stock,
MAX(stock_quantity) AS maximum_stock
FROM products;This query returns the smallest and largest stock quantities in one result row.
SELECT
MIN(created_at) AS earliest_date,
MAX(created_at) AS latest_date
FROM products;This query provides the earliest and latest dates recorded in the table.
You can subtract the minimum value from the maximum value to calculate the range of a numeric column.
SELECT
MIN(price) AS lowest_price,
MAX(price) AS highest_price,
MAX(price) - MIN(price) AS price_range
FROM products;Expected output:
| lowest_price | highest_price | price_range |
|---|---|---|
| 800.00 | 55000.00 | 54200.00 |
The price range is calculated as 55000.00 − 800.00 = 54200.00.
The WHERE clause filters rows before the aggregate functions are evaluated. This allows you to find the minimum or maximum value for a selected category, status, date range, or other condition.
SELECT MIN(price) AS lowest_electronics_price
FROM products
WHERE category = 'Electronics';Expected output:
| lowest_electronics_price |
|---|
| 12000.00 |
Only products belonging to the Electronics category are included in the calculation.
SELECT MAX(price) AS highest_accessories_price
FROM products
WHERE category = 'Accessories';Expected output:
| highest_accessories_price |
|---|
| 3200.00 |
SELECT MIN(price) AS lowest_price_above_5000
FROM products
WHERE price > 5000;This query returns the smallest price among products priced above 5000.
SELECT MAX(price) AS highest_price_in_period
FROM products
WHERE created_at >= '2026-02-01'
AND created_at < '2026-03-01';This query finds the maximum product price among records dated in February 2026. The date condition uses an inclusive start and exclusive end, which is a useful pattern for date and date-time filtering.
SELECT MIN(price) AS lowest_price
FROM products
WHERE category = 'Electronics'
AND stock_quantity > 5;This query returns the minimum price among Electronics products whose stock quantity is greater than five.
The GROUP BY clause divides records into groups and calculates the minimum or maximum value separately for each group. This is useful when comparing different product categories, departments, regions, or customers.
SELECT
category,
MIN(price) AS lowest_price
FROM products
GROUP BY category;Expected output:
| category | lowest_price |
|---|---|
| Accessories | 800.00 |
| Electronics | 12000.00 |
| Furniture | 7500.00 |
Each category appears once, showing its lowest recorded product price.
SELECT
category,
MAX(price) AS highest_price
FROM products
GROUP BY category;Expected output:
| category | highest_price |
|---|---|
| Accessories | 3200.00 |
| Electronics | 55000.00 |
| Furniture | 15000.00 |
SELECT
category,
MIN(price) AS lowest_price,
MAX(price) AS highest_price
FROM products
GROUP BY category;This query provides the price range for every category in a single result set.
SELECT
category,
created_at,
MIN(price) AS lowest_price,
MAX(price) AS highest_price
FROM products
GROUP BY category, created_at
ORDER BY category, created_at;This query groups products by both category and creation date. Each unique combination produces a separate group.
The HAVING clause filters grouped results based on aggregate calculations. Use it when you want to return only groups whose minimum or maximum value meets a condition.
SELECT
category,
MAX(price) AS highest_price
FROM products
GROUP BY category
HAVING MAX(price) > 10000;This query returns only categories whose highest product price is greater than 10000.
SELECT
category,
MIN(price) AS lowest_price
FROM products
GROUP BY category
HAVING MIN(price) < 5000;This query returns categories that contain at least one product with a price below 5000, because their minimum price is below that threshold.
| WHERE | HAVING |
|---|---|
| Filters individual records before grouping. | Filters groups after aggregate calculations. |
| Commonly checks regular column values. | Commonly checks aggregate results. |
WHERE category = 'Electronics' |
HAVING MAX(price) > 10000 |
SELECT
category,
MAX(price) AS highest_price
FROM products
WHERE stock_quantity > 5
GROUP BY category
HAVING MAX(price) > 10000;First, MySQL filters out products whose stock quantity is five or less. It then groups the remaining products by category and returns only groups whose maximum price exceeds 10000.
Aggregate functions return calculated values, while ORDER BY sorts the result rows. LIMIT restricts how many rows are returned.
SELECT
category,
MAX(price) AS highest_price
FROM products
GROUP BY category
ORDER BY highest_price DESC;This query displays categories from the highest maximum price to the lowest.
SELECT
category,
MAX(price) AS highest_price
FROM products
GROUP BY category
ORDER BY highest_price DESC
LIMIT 1;This returns one category with the highest category-level maximum price. If multiple categories share the same value, add a secondary ordering column for predictable selection.
SELECT
category,
MIN(price) AS lowest_price
FROM products
GROUP BY category
ORDER BY lowest_price ASC;This query sorts categories from the lowest minimum price to the highest.
An important distinction is that MIN() and MAX() return the minimum or maximum value, not the entire row containing that value. If you need the associated product name or employee details, you must retrieve the matching record separately.
SELECT product_id, product_name, price
FROM products
WHERE price = (
SELECT MIN(price)
FROM products
);This query returns the product record or records whose price equals the minimum price in the table.
SELECT product_id, product_name, price
FROM products
WHERE price = (
SELECT MAX(price)
FROM products
);This query returns the product record or records whose price equals the maximum price in the table.
A subquery is a query placed inside another SQL statement. In these examples, the inner query calculates the minimum or maximum price, and the outer query retrieves the records matching that value.
SELECT p.product_id, p.product_name, p.category, p.price
FROM products p
JOIN (
SELECT category, MIN(price) AS lowest_price
FROM products
GROUP BY category
) AS category_min
ON p.category = category_min.category
AND p.price = category_min.lowest_price;This query returns the product or products that have the lowest price in each category. If multiple products share the minimum price in a category, all matching products are returned.
The MIN() and MAX() functions are not limited to numeric data. They can also evaluate date and time columns.
SELECT MIN(created_at) AS earliest_date
FROM products;This query returns the earliest non-NULL date in the table.
SELECT MAX(created_at) AS latest_date
FROM products;This query returns the latest non-NULL date in the table.
SELECT
MIN(created_at) AS first_date,
MAX(created_at) AS last_date
FROM products;This query identifies the date range covered by the records.
If a table contains transaction dates, you can group by category and find the date range for each category.
SELECT
category,
MIN(created_at) AS earliest_date,
MAX(created_at) AS latest_date
FROM products
GROUP BY category;This is useful for analyzing when products or records first appeared and when the most recent records were created.
In MySQL, NULL represents a missing or unknown value. The MIN() and MAX() aggregate functions ignore NULL values and calculate their results using non-NULL values.
CREATE TABLE price_test (
item_id INT PRIMARY KEY,
price DECIMAL(10, 2)
);
INSERT INTO price_test (item_id, price)
VALUES
(1, 100.00),
(2, 250.00),
(3, NULL),
(4, 400.00);
SELECT
MIN(price) AS minimum_price,
MAX(price) AS maximum_price
FROM price_test;Expected output:
| minimum_price | maximum_price |
|---|---|
| 100.00 | 400.00 |
The NULL value is ignored, so the minimum is 100.00 and the maximum is 400.00.
SELECT
MIN(price) AS minimum_price,
MAX(price) AS maximum_price
FROM price_test
WHERE price IS NULL;Both functions return NULL because the selected rows contain no non-NULL price values.
SELECT
MIN(price) AS minimum_price,
MAX(price) AS maximum_price
FROM products
WHERE category = 'Books';If no records match the condition, both aggregate functions return NULL. This is different from returning zero, because zero is a numeric value while NULL indicates that no applicable value was found.
SELECT
COALESCE(MIN(price), 0) AS minimum_price,
COALESCE(MAX(price), 0) AS maximum_price
FROM products
WHERE category = 'Books';COALESCE() returns the first non-NULL expression. In this example, it displays zero when an aggregate result is NULL. Use this only when zero is an appropriate reporting default for missing results.
The MIN() and MAX() functions can also work with text columns. For strings, MySQL determines the minimum and maximum according to the column's character set and collation rules.
SELECT MIN(product_name) AS earliest_name
FROM products;The result is the minimum string according to the applicable collation. It should not be interpreted as the product with the lowest price.
SELECT MAX(product_name) AS greatest_name
FROM products;The result is the maximum string according to the applicable collation rules.
MIN() and MAX() when you need the lowest or highest numeric value.Both aggregate functions and sorting can help identify extreme values, but they operate differently.
| Method | Purpose | Example |
|---|---|---|
MIN(price) |
Returns the smallest price value. | SELECT MIN(price) FROM products; |
MAX(price) |
Returns the largest price value. | SELECT MAX(price) FROM products; |
ORDER BY price ASC LIMIT 1 |
Returns one row with the lowest price, subject to the ordering. | SELECT * FROM products ORDER BY price ASC LIMIT 1; |
ORDER BY price DESC LIMIT 1 |
Returns one row with the highest price, subject to the ordering. | SELECT * FROM products ORDER BY price DESC LIMIT 1; |
Use MIN() or MAX() when you need the extreme value itself, such as the lowest price or latest date.
Use ORDER BY when you need to sort records or retrieve the complete row associated with an extreme value. Add a secondary ordering column if ties must be resolved predictably.
Problem: The query refers to a column that does not exist.
SELECT MIN(product_price)
FROM products;If the table has a column named price instead of product_price, MySQL reports an unknown-column error.
Solution: Verify the table structure.
DESCRIBE products;Problem: The query returns only a minimum value, but the developer also needs the product name and ID.
Solution: Use a subquery to identify the minimum value and retrieve the matching records, or use an appropriately ordered query when one row is sufficient.
Problem: The result is NULL.
Possible causes:
Solution: Inspect the data and filter conditions. If required, use COALESCE() to display a default value.
Problem: The query calculates a minimum from the wrong column.
Solution: Confirm which value you need to evaluate. Use MIN(price) for the lowest price and MIN(stock_quantity) for the smallest stock quantity.
Problem: The query produces unexpected grouped values after a JOIN.
Solution: Review the join conditions and ensure the query is grouping at the intended level. Remember that joins can duplicate rows, although duplicate copies of the same value do not change a basic MIN() or MAX() result. Duplicated or incorrectly matched records can still cause other reporting errors.
Problem: A query attempts to filter groups using MIN() or MAX() inside the WHERE clause.
SELECT category, MAX(price) AS highest_price
FROM products
WHERE MAX(price) > 10000
GROUP BY category;This is not the correct way to filter groups by an aggregate value.
Solution: Use HAVING.
SELECT category, MAX(price) AS highest_price
FROM products
GROUP BY category
HAVING MAX(price) > 10000;lowest_price and highest_price.WHERE to limit the records included in the calculation.GROUP BY to calculate minimum and maximum values for categories or other groups.HAVING to filter groups based on aggregate results.LIMIT must return a predictable row.Use the products table to practise the following tasks.
Write a query to find the minimum value in the price column.
Write a query to find the maximum value in the price column.
Display the minimum and maximum values in the stock_quantity column.
Calculate the difference between the highest and lowest product prices.
Find the minimum price among products in the Electronics category.
Find the maximum price among products in the Accessories category.
Display the lowest and highest prices for each category.
Display categories whose maximum product price is greater than 10000.
Find the earliest and latest dates in the created_at column.
Display the product ID, name, and price for every product matching the maximum price.
SELECT MIN(price) AS lowest_price
FROM products;SELECT MAX(price) AS highest_price
FROM products;SELECT
MIN(stock_quantity) AS minimum_stock,
MAX(stock_quantity) AS maximum_stock
FROM products;SELECT
MAX(price) - MIN(price) AS price_range
FROM products;SELECT MIN(price) AS lowest_electronics_price
FROM products
WHERE category = 'Electronics';SELECT MAX(price) AS highest_accessories_price
FROM products
WHERE category = 'Accessories';SELECT
category,
MIN(price) AS lowest_price,
MAX(price) AS highest_price
FROM products
GROUP BY category;SELECT
category,
MAX(price) AS highest_price
FROM products
GROUP BY category
HAVING MAX(price) > 10000;SELECT
MIN(created_at) AS earliest_date,
MAX(created_at) AS latest_date
FROM products;SELECT product_id, product_name, price
FROM products
WHERE price = (
SELECT MAX(price)
FROM products
);Project objective: Create a product analysis report using MySQL's MIN() and MAX() functions.
SELECT
MIN(price) AS lowest_price,
MAX(price) AS highest_price,
MAX(price) - MIN(price) AS price_range
FROM products;SELECT
category,
MIN(price) AS lowest_price,
MAX(price) AS highest_price,
MAX(price) - MIN(price) AS category_price_range
FROM products
GROUP BY category
ORDER BY highest_price DESC;SELECT product_id, product_name, category, price
FROM products
WHERE price = (SELECT MIN(price) FROM products)
OR price = (SELECT MAX(price) FROM products);SELECT
MIN(created_at) AS earliest_product_date,
MAX(created_at) AS latest_product_date
FROM products;After completing this project, you should be able to analyze product price ranges, identify extreme values, compare categories, and generate useful summaries for business reporting.
Answer: MIN() is an aggregate function that returns the smallest non-NULL value from the selected values.
Answer: MAX() is an aggregate function that returns the largest non-NULL value from the selected values.
Answer: Yes. Both functions can be included in one SELECT statement to calculate the minimum and maximum values together.
Answer: Yes. Both functions ignore NULL values. If no non-NULL values are available in the selected set, they return NULL.
Answer: Yes. The WHERE clause filters the records before the aggregate functions calculate their results.
Answer: GROUP BY divides records into groups and calculates a minimum or maximum value separately for each group.
Answer: WHERE filters individual rows before aggregation, while HAVING filters groups after aggregate calculations.
Answer: Yes. MIN() can return the earliest date, while MAX() can return the latest date among the selected non-NULL dates.
Answer: No. They return an aggregate value. To retrieve the full row associated with an extreme value, use a subquery or another suitable query pattern.
Answer: Use a subquery to calculate the maximum price and retrieve records matching that value.
SELECT product_id, product_name, price
FROM products
WHERE price = (SELECT MAX(price) FROM products);Answer: MAX() returns the maximum value, whereas ORDER BY DESC sorts records from highest to lowest. ORDER BY with LIMIT can retrieve a row, while MAX() returns the aggregate value itself.
Answer: Yes. They compare strings according to the applicable character set and collation rules.
MIN(column_name) returns the smallest non-NULL value.MAX(column_name) returns the largest non-NULL value.WHERE to filter rows before calculating the result.GROUP BY to calculate values for individual groups.HAVING to filter groups based on aggregate results.COALESCE() when a default display value is required.The MIN() and MAX() functions are essential MySQL aggregate functions for finding the smallest and largest values in a dataset. They can be applied to numeric columns, dates, times, and text values according to the applicable comparison rules.
By combining these functions with WHERE, GROUP BY, HAVING, ORDER BY, and subqueries, you can identify price ranges, compare categories, locate the earliest and latest dates, and retrieve records associated with extreme values.
Practise the examples and exercises in this lesson to develop confidence in using MIN() and MAX() for real-world MySQL database tasks.
Learn More: Explore the MySQL Training Course and register for a course demo to continue learning SQL and database concepts.
