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:
expression
)
[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
FROM
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:
344
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
FROM
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:
hello
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:
MAX
-------
4.99
(1 ROW)
The above returns the highest amount from the column.
Conclusion
This article discussed the SQL max() function in Standard SQL. Using this function, you can determine the maximum value within a provided set.
