SQL MIN() and MAX() Functions
The MIN() and MAX() functions in SQL are aggregate functions used to find the smallest and largest values, respectively, in a set of numbers. They are essential tools for summarizing numerical data.
MIN() and MAX(): Definition and Usage
These functions are very useful for getting a quick understanding of the range of your numerical data. MIN() returns the smallest value, while MAX() returns the largest value in a specified column. Both ignore NULL values.
Syntax
Syntax
SELECT MIN(column_name) FROM table_name WHERE condition;
SELECT MAX(column_name) FROM table_name WHERE condition;
Example Database: Products Table
The examples below use this sample 'Products' table:
| ProductID | ProductName | SupplierID | CategoryID | Unit | Price |
|---|---|---|---|---|---|
| 1 | Chais | 1 | 1 | 10 boxes x 20 bags | 18 |
| 2 | Chang | 1 | 1 | 24 - 12 oz bottles | 19 |
| 3 | Aniseed Syrup | 1 | 2 | 12 - 550 ml bottles | 10 |
| 4 | Chef Anton's Cajun Seasoning | 2 | 2 | 48 - 6 oz jars | 22 |
| 5 | Chef Anton's Gumbo Mix | 2 | 2 | 36 boxes | 21.35 |
Examples
Finding the Minimum Price
This query finds the lowest price in the 'Price' column.
Syntax
SELECT MIN(Price) FROM Products;
Output
10
Finding the Maximum Price
This query finds the highest price in the 'Price' column.
Syntax
SELECT MAX(Price) FROM Products;
Output
22
Using AS to Name the Output Column
This adds a descriptive name ("SmallestPrice") to the output column.
Syntax
SELECT MIN(Price) AS SmallestPrice FROM Products;
Output
SmallestPrice
-------------
10
Using MIN() with GROUP BY
This finds the minimum price for each category.
Syntax
SELECT MIN(Price) AS SmallestPrice, CategoryID
FROM Products
GROUP BY CategoryID;
Output
SmallestPrice | CategoryID
------------- | -----------
10 | 2
18 | 1