SQL Standard

SQL Max Function

It is nearly impossible to think of SQL without the MAX() function— an aggregate function that allows you to pass a collection of values and return the maximum provided set.

This article will discuss how we can use the max() function in Standard SQL. Keep in mind that various database engines may provide variations of this function.

What is SQL Max() function?

The max() function in SQL is an aggregate function. It takes a numerical set and returns the largest value from the provided set. You can pass a column containing numerical values or an expression that returns a numerical value(s).

Since the function returns a single value, we can think of it as a scalar function.

The function syntax is shown below:

[OVER (...)]

The function takes one argument containing non-null numeric values. Therefore, the argument must have numeric values.

The function will return a similar data type as the provided input values.

Example Usage

The following are sample examples of using the max() function in SQL.

Example 1
A simple example of usage of the max() function is shown below:

  MAX(nums) AS MAX
  UNNEST([100,23,45,12,192,344,32,56,76]) AS nums;

The example above uses the max function to get the largest number in an array of numerical values. The return value is:


Example 2
Although it is recommended to pass numeric values as the parameter of the function, it can work with non-numeric values, as shown in the example below:

  MAX(str) AS MAX
  UNNEST(['a','b','c', 'hello']) AS str;

In this example, the function will compare the number of characters in each string value. The resulting value is as shown:


Example 3
We can also calculate the max value in a column. For example, suppose we have a table containing payment details, as shown in the example below:

We can calculate the maximum amount using the max function as shown below:

SELECT MAX(amount) FROM payment;
(1 ROW)

The above returns the highest amount from the column.


This article discussed the SQL max() function in Standard SQL. Using this function, you can determine the maximum value within a provided set.

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list