Online College Courses for Credit

+
MAX & MIN to Find Extremes

MAX & MIN to Find Extremes

Rating:
(0)
Author: Sophia Tutorial
Description:

Compose a query that determines the largest and smallest values in a given table/column.

(more)
See More
Fast, Free College Credit

Developing Effective Teams

Let's Ride
*No strings attached. This college course is 100% free and is worth 1 semester credit.

47 Sophia partners guarantee credit transfer.

299 Institutions have accepted or given pre-approval for credit transfer.

* The American Council on Education's College Credit Recommendation Service (ACE Credit®) has evaluated and recommended college credit for 33 of Sophia’s online courses. Many different colleges and universities consider ACE CREDIT recommendations in determining the applicability to their course and degree programs.

Tutorial

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

File:11285-1330-1.png

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;

File:11286-1330-2.png

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

File:11287-1330-3.png


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;

File:11288-1330-4.png

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;

File:11289-1330-5.png

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.

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