Redshift MAX Function

The MAX function in Redshift can be used to get the highest value from the given set of rows. It can be applied not only to INTEGER data types but many others. You can also pass a DISTINCT or ALL parameter, but it does not have any effect on the result of the MAX function. In this blog, we will see how the MAX function can be used to get maximum values from different rows with examples.

Syntax of MAX Function

The syntax to use the MAX function to get the maximum value from a set of rows is pretty simple. It can be used by following the given syntax.

SELECT MAX( [DISTINCT | ALL] expression)

Expression

The expression is the target column from which the MAX function gets the maximum value. Following is the list of data types that the MAX function accepts for expression:

BIGINT
INTEGER
DOUBLE PRECISION
DECIMAL
REAL
VARBYTE
CHAR
DATE
TIMESTAMP
SUPER
TIMESTAMPTZ
TIME
VARCHAR
SMALLINT
TIMETZ

DISTINCT | ALL

Along with the expression, you can also pass either the DISTINCT or ALL parameter in the MAX function. When the DISTINCT parameter is passed, the MAX function removes duplicate values and gets the maximum value from the column. On the other hand, when we pass the ALL parameter, the function does not remove duplicate values and gets the maximum value.

Examples of MAX Function

In this section, we will see several examples of applying the MAX function in different data types in Redshift. We will apply the MAX function to the following data types in Redshift.

INTEGER
VARCHAR
DATE

Applying MAX Function on an INTEGER Data Type

When the MAX function is applied to an INTEGER data type, it returns the highest value from the column of integers. After getting the MAX value from the column, we can perform further analysis on it using Redshift queries. The following query will return the highest value from the id column of the users’ table.

SELECT MAX(id) AS max_id FROM users;

max_id
332

Applying MAX Function on VARCHAR Data Type

The MAX function can also be applied to the VARCHAR data type to get the value at the end of the alphabetical order. When applied to the VARCHAR data type, the MAX function sorts the values in alphabetical order and then returns the last value in alphabetical order. The following query can be executed on the Redshift cluster to get the last name in alphabetical order from the username column of the users’ table.

SELECT MAX(username) AS last_user FROM users;

last_user
William

Applying MAX Function on DATE Data Type

The MAX function can be used to get the latest date from different values of the DATE data type. The MAX function will sort all the values of the DATE data type and then return the latest date from these values.

In order to get the date of birth of the youngest employee from multiple employees, use the following query in Redshift. It will get the date of birth from the date_of_birth column of the users’ table.

SELECT MAX(date_of_birth) AS youngest_employee FROM users;

youngest_employee
2000-02-20

Using MAX Function With WHERE and AND Statement

So far, we have applied the MAX function to different data types to get the maximum values. Now, we will use the MAX function with the WHERE statement to get the information about specific rows from a table.

In order to get all the details of the youngest employee from the users’ table, the MAX function can be used along with the WHERE statement in Redshift. When executed on the Redshift, the following query will get the required result.

SELECT * FROM users WHERE date_of_birth = (SELECT MAX(date_of_birth) FROM users);

The previous query will first get the date of birth of the youngest employee and then get all the user’s details using the SELECT statement.

Similarly, we can apply the MAX function with WHERE and AND statements to get the desired result. In order to get the details of the youngest employee with an employee status of 1, execute the following query in the Redshift.

SELECT * FROM users WHERE date_of_birth = (SELECT MAX(date_of_birth) FROM users) AND employee_status = 1;

When executed in the Redshift cluster, the previous query will return all the details of the youngest user with an employee status of 1.

Conclusion

The MAX function in Redshift can be used to get the highest value from a column. This function can be applied to different data types, such as INTEGER, VARCHAR, and DATE. This blog describes how we can get our desired result by using the MAX function in Redshift.