The MIN and MAX functions help us find answers to problems where we may need to know the highest and lowest values in a table or result set. The most common use for the MIN and MAX function is for numeric columns, such as a price or invoice total. The structure of the command looks like the following:
SELECT MIN(<columnname>)
FROM <tablename>;
For example, if we wanted to find the smallest total in the invoice table, we would run the following:
SELECT MIN(total)
FROM invoice;
We can add more complexity to the query because you can have as many aggregate functions as you want in the SELECT clause. To add the MAX of the invoice total in the same statement, we would add it within the SELECT clause:
SELECT MIN(total), MAX(total)
FROM invoice;
We can also combine it with additional filters within the WHERE clause. For example, we may want to look at the minimum and maximum invoice totals when the billing country is set to Canada:
SELECT MIN(total), MAX(total)
FROM invoice
WHERE billing_country = 'Canada';
The MIN and MAX can also be used for other data types outside of numeric values. With text-based columns, using the MIN would return the minimum value in alphabetical order, whereas MAX would return the maximum value in alphabetical order.
For example, if we looked at the MIN and MAX of the country in alphabetical order:
SELECT MIN(country), MAX(country)
FROM customer;
The MIN and MAX can also work for dates as well. Dates in databases are stored as a day number, which focuses on the number of days that have passed since a specific point in time. As a day number, the date for yesterday is one less than the day number for today. Older dates therefore end up being smaller numbers than future dates. So the oldest date is actually the MIN, or smallest date, whereas the most recent date is the MAX, or largest date.
For example, if we wanted to find the oldest and youngest employee, we could run the following:
SELECT MIN(birth_date), MAX(birth_date)
FROM employee;
Notice that the oldest employee, or the one with the earliest date, is returned using the MIN aggregate function. The youngest employee, or the one with the latest date, is returned using the MAX aggregate function.
The min and the max aggregate functions can be extremely useful to be able to find the minimum value and the maximum value based on a specific group or set. So in this case here, we're going to take a look at the invoice table. We're just selecting the invoice ID, billing country, and total.
So in this case here, if we're trying to select from the invoice table of the min and max totals, it'll look at the entire table. So in this case here, if we try to select that, it'll show the minimum amount for the total is going to be one. And the maximum is going to be 26. However, we can actually expand on this to be able to search on specific criteria, utilizing the where clause to limit the result set.
Let's first take a look at, say like France as the billing country. It'll look something like this. We'll look at the same things. If we're trying to find the minimum and maximum of the totals when the country is France, we can certainly scroll through and look for the highest number. However, that's certainly not something that's always going to be feasible, especially when we have a lot of data.
What we can do is utilize min and max as well in the same way. With that condition in this case, we're having min with the total and then max with a total. And then we run it. We have the minimum value being one, the maximum value being 17.
Typically, we do utilize the min and max with numeric values. However, you can certainly utilize it with dates to find the minimum maximum dates, as well as a minimum and maximum values based on characters, in which it'll search it based on the alphabetical order of the characters. So in this case here, if we're trying to find the min and max country, it'll show Argentina being the minimum starting with A and then max being USA.
Source: Authored by Vincent Tran