FB

Oracle MAX FunctionIn this article, I’m going to look at the Oracle MAX function and explain how it can be used, along with some examples.

Purpose of the Oracle MAX Function

The concept of the MAX function is quite simple.

It returns the maximum value of the expression you provide to it.

It’s a standard SQL function so it works in SQL Server and MySQL.

However, I’ll be looking at the Oracle version of it.

 

Syntax

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

The syntax of Oracle MAX as an aggregate function is:

MAX ( [DISTINCT/ALL] expr)

The syntax of Oracle MAX as an analytic function is:

MAX ( [DISTINCT/ALL] expr) 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:

  • expr (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 Oracle 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.

 

What Does The “Oracle MAX Over” Do?

Using the keyword OVER within the Oracle 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 below, I show how this is used.

 

Examples of the Oracle 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_IDFIRST_NAMELAST_NAMEFEES_PAIDENROLMENT_DATE
1JohnSmith10001/02/2015
2SusanJohnson15012/01/2015
3TomCapper32006/03/2015
4MarkHolloway41020/01/2015
5StevenWebber8009/03/2015
6JulieArmstrong012/02/2015
7MichelleRandall23/01/2015
8AndrewCooper40004/03/2015
9RobertPickering10030/01/2015
10TanyaHall15028/01/2015

 

Example 1

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

SELECT MAX(fees_paid) AS MAX_TEST
FROM student;

Result:

MAX_TEST
410

The maximum value in this set is 410.

 

Example 2

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

This example performs a MAX on a date field.

SELECT MAX(enrolment_date) AS MAX_TEST
FROM student;

Result:

MAX_TEST
09/03/2015

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_MONTHMAX_FEES
MAR400
FEB100
JAN410

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_NAMELAST_NAMEENROLMENT_DATEMAX_TEST
JulieArmstrong12/02/2015100
TomCapper06/03/2015400
AndrewCooper04/03/2015400
TanyaHall28/01/2015410
MarkHolloway20/01/2015410
SusanJohnson12/01/2015410
RobertPickering30/01/2015410
MichelleRandall23/01/2015410
JohnSmith01/02/2015100
StevenWebber09/03/2015400

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.

 

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.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Image courtesy of digitalart / FreeDigitalPhotos.net

Get Your SQL Cheat Sheets Now:

Want Some SQL Cheat Sheets?

sql-cheat-sheet-images-v2

Download the SQL Cheat Sheets: common commands and syntax - to save you time.

You'll get them for Oracle, SQL Server, MySQL, and PostgreSQL.

Print them or use them as an easy reference.