PostgreSQL MAX function.

Preamble

PostgreSQL MAX function returns the maximum value of the expression.

Syntax of the max function in PostgreSQL

SELECT max(aggregate_expression_id)
FROM tabs
[WHERE conds];

Or the syntax of the max function when grouping results into one or more columns:

SELECT expression1_id,2_id,..._n_id,
max(aggregate_expression_id)
FROM tabs
[WHERE conds]
GROUP BY expression1_id,2_id,..._n_id;

Parameters and arguments of the function

• expression1_id, expression2_id,… expression_n_id – Expressions that are not enclosed in the max function and must be included in the GROUP BY operator at the end of the SQL query.
• aggregate_expression_id – This is the column or expression from which the maximum value will be returned.
• tabs – These are the tables from which you want to get the records. At least one table must be specified in the FROM operator.
• WHERE conditions – Optional. These are the conditions that must be met to select records.

The max function can be used in the following PostgreSQL versions

PostgreSQL 11, PostgreSQL 10, PostgreSQL 9.6, PostgreSQL 9.5, PostgreSQL 9.4, PostgreSQL 9.3, PostgreSQL 9.2, PostgreSQL 9.1, PostgreSQL 9.0, PostgreSQL 8.4.

Single Expression Example

Let’s look at some examples of max functions to understand how to use max in PostgreSQL.

For example, you might want to select all entries with a maximum quantity from an inventory table as max(quantity) AS “Highest Quantity”.

SELECT max(quantity) AS "Highest Quantity"
FROM inventory;

In this example of the max function, we added the expression max(quantity) AS “Highest Quantity”. As a result, “Highest Quantity” will be displayed as the field name when the result set is returned.

Example using GROUP BY

In some cases, you will need to use the GROUP BY operator with the max function.

For example, you can also use the max function to return a department and the maximum quantity from the inventory table.

SELECT depart, max(quantity) AS "Highest Quantity"
FROM inventory
GROUP BY depart;

Since your SELECT operator has one column that is not encapsulated in the max function, you should use the GROUP BY operator. That’s why the department field shall be specified in the GROUP BY operator.

PostgreSQL: Calculating Min, Max & Average

About Enteros

Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.