MAX & MIN to Find Extremes

1. MIN and MAX on numeric values

The MIN and MAX functions help us find answers to problems where we may need to find the highest and lowest values in a table or result set. The most common use for the MIN and MAX 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 find the largest value in the same 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';

2. MIN and MAX on other data types

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 would be one less than the day number for today. Older dates end up being smaller than future dates so the oldest date would actually be the MIN or smallest date whereas the largest date would be the MAX or latest 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.

try it

Your turn! Open the SQL tool by clicking on the LAUNCH DATABASE button below. Then enter in one of the examples above and see how it works. Next, try your own choices for which columns you want the query to provide.