SQL MAX Function Explained with Examples

The SQL MAX function can be used to find the highest number in a list of numbers. Learn how to use it and see some examples in this article.

What is the SQL MAX Function?

The SQL Max function returns the maximum value of the expression you provide to it. It’s good for finding the highest or maximum value in a column. It can work with numbers or dates.

It’s a standard SQL function so it works in major databases such as Oracle, SQL Server, MySQL, and PostgreSQL.

 

SQL MAX Syntax

Just like with the SUM function, you can use the MAX function as an aggregate function or an window function.

The syntax of SQL MAX as an aggregate function is:

MAX ( [DISTINCT/ALL] expression)

The syntax of SQL MAX as an analytic function is:

MAX ( [DISTINCT/ALL] expression) OVER (analytic_clause)

Almost every time I’ve seen MAX used has been as an aggregate function.

 

Parameters

The parameters of the MAX function are:

• expression (mandatory): The expression that is being considered for the MAX function. This can be a column name, a set of values, or any other kind of valid expression.
• analytic_clause (optional): This is used when you’re using MAX as an analytic function. It works similar to grouping your MAX function, but it’s a bit different.

Examples of the MAX Function

Here are some examples of the MAX function. I find that examples are the best way for me to learn about code, even with the explanation above.

First, let’s look at our student table.

SELECT * FROM student;

Results:

STUDENT_ID	FIRST_NAME	LAST_NAME	FEES_PAID	ENROLMENT_DATE
1	John	Smith	100	01/02/2021
2	Susan	Johnson	150	12/01/2021
3	Tom	Capper	320	06/03/2021
4	Mark	Holloway	410	20/01/2021
5	Steven	Webber	80	09/03/2021
6	Julie	Armstrong	0	12/02/2021
7	Michelle	Randall		23/01/2021
8	Andrew	Cooper	400	04/03/2021
9	Robert	Pickering	100	30/01/2021
10	Tanya	Hall	150	28/01/2021

 

Example 1 – Simple MAX

This example shows a simple use of the MAX on a number field.

SELECT MAX(fees_paid)
FROM student;

Result:

MAX(FEES_PAID)

410

The maximum value in this set is 410.

 

Example 2 – MAX with Alias

This example shows a simple use of the MAX on a number field with a column alias to make it more readable.

SELECT MAX(fees_paid) AS MAX_TEST
FROM student;

Result:

MAX_TEST

410

The maximum value in this set is 410.

 

Example 2 – MAX with Text

This example shows how a MAX function would work on a text column (a VARCHAR).

SELECT MAX(first_name) AS MAX_TEST
FROM student;

Result:

MAX_TEST

Tom

The result is Tom. MAX on a VARCHAR column will find the last value as if they were sorted alphabetically.

 

Example 3 – MAX with Date

This example performs a MAX on a date field.

SELECT MAX(enrolment_date) AS MAX_TEST
FROM student;

Result:

MAX_TEST

09/03/2021

The date of March 9 is shown as that is the last date in the column. It works similar to using a number field, where it finds the latest or last date in the supplied data.

 

Example 4

This example uses the GROUP BY clause with MAX, just like with any other aggregate function.

SELECT TO_CHAR(enrolment_date, 'MON') AS ENROLMENT_MONTH,
MAX(fees_paid) as MAX_FEES
FROM student
GROUP BY TO_CHAR(enrolment_date, 'MON');

Result:

ENROLMENT_MONTH	MAX_FEES
MAR	400
FEB	100
JAN	410

This shows the months of enrolment, along with the MAX of fees paid.

 

Example 5

This example uses the MAX function as an analytic function. I’ve used the OVER clause to get the MAX of fees_paid for each of the enrolment months.

SELECT first_name,
last_name,
enrolment_date,
MAX(fees_paid) OVER (PARTITION BY TO_CHAR(enrolment_date, 'MON'))  AS MAX_TEST
FROM student
ORDER BY last_name, first_name

Result:

FIRST_NAME	LAST_NAME	ENROLMENT_DATE	MAX_TEST
Julie	Armstrong	12/02/2021	100
Tom	Capper	06/03/2021	400
Andrew	Cooper	04/03/2021	400
Tanya	Hall	28/01/2021	410
Mark	Holloway	20/01/2021	410
Susan	Johnson	12/01/2021	410
Robert	Pickering	30/01/2021	410
Michelle	Randall	23/01/2021	410
John	Smith	01/02/2021	100
Steven	Webber	09/03/2021	400

This table shows all of the employees, as well as the MAX of the fees_paid for each month of enrolment. Unlike using a GROUP BY, this method shows all student records.

You can do some more complicated task with the MAX function, such as selecting all rows that meet a maximum value or selecting the maximum value based on a subgroup.

 

What Does The “MAX OVER” Do?

Using the keyword OVER within the MAX query allows you to use it as an analytic function. So, this means that you can view the MAX value in a different way.

The OVER clause lets you specify what you want the MAX function to be grouped by, or what the MAX is contained to. Normally, the MAX function gets the maximum of everything, but you can use OVER to specify you want the maximum in a certain group (e.g. MAX salary of employees in a department).

In the examples, I show how this is used.

 

Similar Functions

Some functions which are similar to the MAX function are:

• MIN – This function gets the lowest of the supplied expression. It’s the opposite of the MAX function.
• AVG – This function gets the average value of a supplied expression.
• SUM – This function adds up all of the values supplied.
• COUNT – This function counts the values supplied.

You can find a full list of Oracle SQL functions here. Other function lists are coming soon.