This lecture covers the MIN() and MAX() aggregate functions.
To round out our introduction to aggregate functions, we will look at two simple but very useful functions: MIN() and MAX().
These functions do exactly what their names suggest: they find the minimum (smallest) and maximum (largest) value in a given column.
Finding the Minimum Value with `MIN()`
The MIN() function returns the smallest value from a set of rows. This can be used on numeric columns to find the lowest number, or on text columns to find the value that comes first alphabetically.
Using our `Sales` table, let’s find the price of the cheapest item we have sold.
SELECT MIN(Price) AS CheapestItemPrice FROM Sales;This will scan the `Price` column (0.50, 0.30, 0.50, 0.60) and return the single smallest value: 0.30.
Finding the Maximum Value with `MAX()`
Similarly, the MAX() function returns the largest value from a set of rows.
Let’s find the price of the most expensive item we have sold
SELECT MAX(Price) AS MostExpensiveItemPrice FROM Sales;This will scan the `Price` column and return the single largest value: 0.60.
Using `MIN()` and `MAX()` on Text
When used on a text column, these functions work alphabetically. MIN() will find the value that comes first in alphabetical order, and MAX() will find the value that comes last.
Let’s find the first and last item in our `Sales` table alphabetically.
SELECT MIN(ItemName) AS FirstAlphabetical, MAX(ItemName) AS LastAlphabetical FROM Sales;This will return two columns. `FirstAlphabetical` will be ‘Apple’ and `LastAlphabetical` will be ‘Orange’.
Combining with `WHERE`
Of course, you can also combine these functions with a WHERE clause. For example, what is the most expensive item we sold that was not an Orange?
SELECT MAX(Price) FROM Sales WHERE ItemName != 'Orange';This would return 0.50, since it would only consider the prices of the Apples and Bananas.
In the Next Lecture…
We have learned how to use aggregate functions to get a single summary value for an entire table or a filtered subset. But what if we want to get summary values for different *groups* of data within the same query? In the next lecture, we will introduce the incredibly powerful GROUP BY clause, which allows us to do exactly that.
