Use Sophia to knock out your gen-ed requirements quickly and affordably. Learn more
×

MAX & MIN to Find Extremes

Author: Sophia

what's covered
This tutorial explores using the aggregate functions MIN and MAX to find the largest and smallest values in two parts:
  1. MIN and MAX on Numeric Values
  2. MIN and MAX on Other Data Types

1. MIN and MAX on Numeric Values

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;

Query Result Example

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;

Query Result Example

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';

Query Result Example


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;

Query Result Example

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;

Query Result Example

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.


Video Transcript

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.

summary
The MIN and MAX aggregate functions provide us with the smallest and largest values in a result set.

Source: Authored by Vincent Tran