PostgreSQL MAX Function: Get Maximum Value in a Set

Still, developing custom functions to find the largest value in a column? or struggling to cope with numerous bugs while deriving summary statistics using user-defined functions?

If this is the case, congratulations, you’ve landed in the right place!
This blog can act as the one-stop-destination that will save you from all the hassles of working with the PostgreSQL MAX() Function.

In this article, you’ll learn about PostgreSQL MAX() Function, its syntax, and a few practical examples. Later, you will discover a quick overview of PostgreSQL Aggregate Function, including its types and usage.
Tuck in your seat belt, and Let’s get started!

Table of Contents

• What are Aggregate Functions in PostgreSQL?
• What is PostgreSQL MAX() function?
• How does MAX() function work in PostgreSQL?
  • Example#1
    • Case#1: PostgreSQL MAX() function without any clause
    • Case#2: PostgreSQL MAX() function with ‘DISTINCT’ clause
    • Case#3: PostgreSQL MAX() function with ‘GROUP BY’ clause
    • Case#4: PostgreSQL MAX() function with Sub-Query 
  • Example#2
    • Case#1: PostgreSQL MAX() function without any clause
    • Case#2: PostgreSQL MAX() function with ‘DISTINCT’ clause
    • Case#3: PostgreSQL MAX() function with ‘GROUP BY’ clause
    • Case#4: PostgreSQL MAX() function with Sub-Query 
• Conclusion

What are Aggregate Functions in PostgreSQL?

An aggregate function generates summary results, simultaneously operating on several database rows. The output is generated based on the group of rows. 
Note: By default, all the rows in the table are treated as one group.

The GROUP BY clause of the SELECT statement has divided the rows into smaller groups. In such cases, the aggregate functions, such as maximum, minimum, average, etc., perform the calculations on each group, after which the resultant values/row is retrieved as the output. 

All of the aggregate functions in conventional SQL are available in PostgreSQL as follows:

• avg( expression  ): Returns the average of the expression values from all rows in a group.
• count( expression  ): Returns the number of values per each aggregated group of rows for which expression is not NULL
• max( expression  ): Returns the maximum value of the expression in the grouped rows
• min( expression  ): Returns the minimum value of the expression in the grouped rows

Do you know? A Regular Function produces a result for each row, whereas an Aggregate Function yields a single result from a collection of related rows.

What is Aggregate Expression?

Aggregate expressions are used to describe an expression from the result set produced by the SELECT statement when calling an aggregate function. Like a regular SQL statement, an aggregate expression can be preceded by either the ALL or the DISTINCT keyword.

Note: You can use aggregate functions as expressions only in the SELECT & HAVING clause.

Syntax:

aggregate_name (expression [,...] [ order_by_clause] )
OR

aggregate_name (ALL expression [,...] [ order_by_clause] )
OR

aggregate_name (DISTINCT expression [,...] [ order_by_clause] )
OR

aggregate_name (* )

Note: Rows whose evaluated aggregate expression contains NULL values will not be assessed by an aggregate function (except the count() function).

What is PostgreSQL MAX() function?

The PostgreSQL MAX() function retrieves the largest value, indicated by expression in a set of grouped rows. PostgreSQL MAX() function accepts an expression in a data type (numeric, String, Date, or time) and retrieves and returns the maximum as a value of the same data type specified in the expression.

Syntax:

MAX(column_name (Name of the column which was used with the MAX() function to get the maximum value from the set of values.))

OR

Select column_name1, column_name2, …, MAX(column_name or aggregate expression) from table_name [ where condition ]group by column_name;

OR

Select MAX(column_name or aggregate expression) from table_name [ where condition]

OR

MAX (Distinct column_name (Name of the column which was used with MAX() function to get the maximum value from the set of values.))

• MAX(): Aggregate function Type returns the largest value from a data set.
• DISTINCT: Describes to retrieve only the distinct values from a column and avoid duplicacy(Optional).
• Column_name: Defines the column name on which computation is done to find the maximum value from the column. 
• Table_name: Defines the name of the table.
• SELECT: Used to retrieve data from the table based on columns selected.
• WHERE clause: To filter out the results of the SELECT statement.
• GROUP BY Clause: To retrieve results for a group of rows defined in the GROUP BY condition. 

How does MAX() function work in PostgreSQL?

The PostgreSQL MAX() function’s working is demonstrated below with the help of 2 practical examples. 

Example#1

The following Sample Table(employees) is used to demonstrate the working & usage of the PostgreSQL MAX() function in the below-mentioned example.

• Case#1: PostgreSQL MAX() function without any clause
• Case#2: PostgreSQL MAX() function with ‘DISTINCT’ clause
• Case#3: PostgreSQL MAX() function with ‘GROUP BY’ clause
• Case#4: PostgreSQL MAX() function with Sub-Query 

Sample Table:

 id | name | gender | employment_date | age
----+----------------------+----------+-----------------+-----
  1 | Erick Seward	 | Male | 2019-05-19 | 32
  2 | Olivia Lawson	 | Female | 2019-06-18 | 30
  3 | Edison Rowland | Male | 2017-03-19 | 28
  4 | Willard Anderson | Male | 2018-06-02 | 34
  5 | Eleanor Harmon | Female | 2019-12-12 | 37
  6 | Jade Bowman | Female | 2019-10-29 | 34
  7 | Bradley White | Male | 2018-03-13 | 33
  8 | Edward Harley | Male | 2017-02-13 | 33

(8 ROWS)

Case#1: PostgreSQL MAX() function without any clause

Let’s find out the highest value from the column ‘id’. Here, we have used the MAX() function to extract the highest integer from a list of values.

SELECT MAX(id) FROM employees;
 MAX
—--OUTPUT—--
   8
(1 ROW)

Case#2: PostgreSQL MAX() function with ‘DISTINCT’ clause

The task of the PostgreSQL MAX() function is to obtain a single result from a set of data values. In this case, we have used the DISTINCT clause; hence the MAX() function will only return the largest number from the set of distinct values.

SELECT MAX(DISTINCT age) FROM employees;
 MAX
—--OUTPUT—--
  37
(1 ROW)

Case#3: PostgreSQL MAX() function with ‘GROUP BY’ clause

The following code will demonstrate the maximum value from the “name” column and group the results by Gender(Male/Female).

SELECT MAX(name) AS name, gender FROM employees GROUP BY gender;
—--OUTPUT—--
       name | gender
------------------+--------
 Olivia Lawson | Female
 Willard Anderson | Male
(2 ROWS)

Case#4: PostgreSQL MAX() function with Sub-Query 

The following example illustrated the usage of the PostgreSQL MAX() function in a sub-query.

• SELECT the maximum value by Integer

This example will demonstrate selecting the record from the employees table by obtaining the maximum values from the column id.

SELECT * FROM employees WHERE id = (SELECT MAX(id) FROM employees);
—--OUTPUT—--
 id | name | gender | employment_date | age
----+---------------+--------+-----------------+-----
 8 | Edward Harley | Male | 2017-02-13 | 33

(1 ROW)

Note: MAX() function selects the highest integer from the records; hence the last record is pulled out.

• SELECT the maximum value by String

This example describes selecting a record by pulling the Largest name(alphabetically).

SELECT * FROM employees WHERE name = (SELECT MAX(name) FROM employees);
—--OUTPUT—--
 id | name | gender | employment_date | age
----+------------------+--------+-----------------+-----
  4 | Willard Anderson | Male | 2018-06-02 | 34
(1 ROW)

Note: Here, the MAX() function will look at the String’s first character and select the record with the largest alphabet(moving from A to Z).

• SELECT the maximum value by Date

The following example demonstrates how the SELECT statement works to fetch a record based on the largest value from the employment_date column:

SELECT * FROM employees WHERE employment_date = (SELECT MAX(employment_date) FROM employees);
—--OUTPUT—--
 id | name | gender | employment_date | age
----+----------------+--------+-----------------+-----
  5 | Eleanor Harmon | Female | 2019-12-12 | 37
(1 ROW)

Note: MAX() function will return the record with the most recent Date.

Example#2

The following Sample Table(products) is used to demonstrate the working & usage of the PostgreSQL MAX() function in the below-mentioned example.

• Case#1: PostgreSQL MAX() function without any clause
• Case#2: PostgreSQL MAX() function with ‘DISTINCT’ clause
• Case#3: PostgreSQL MAX() function with ‘GROUP BY’ clause
• Case#4: PostgreSQL MAX() function with Sub-Query 

Sample Table:

+----------+--------------------------------------------+--------------+
| sno | productname | productprice |
+----------+--------------------------------------------+--------------+
| 1001 | Left handed screwdriver | 250.99 |
| 1001 | Right handed screwdriver | 250.99 |
| 1001 | Long Weight (blue) | 140.75 |
| 1001 | Long Weight (green) | 110.99 |
| 1002 | Sledge Hammer | NULL |
| 1003 | Chainsaw | 245.00 |
| 1003 | Straw Dog Box | 55.99 |
| 1004 | Bottomless Coffee Mugs (4 Pack) | 19.99 |
+----------+--------------------------------------------+--------------+

Case#1: PostgreSQL MAX() function without any clause

Let’s find out the highest value from the column ‘sno .’Here, we have used the MAX() function to extract the highest integer from a list of values.

SELECT MAX(sno) 
FROM products;
—--OUTPUT—--
1004

Case#2: PostgreSQL MAX() function with ‘DISTINCT’ clause

Let’s demonstrate the working of the PostgreSQL MAX() function by using the DISTINCT clause which results in the largest value among the set of only distinct values.

SELECT MAX(DISTINCT productprice)
 FROM products;
—--OUTPUT—--
250.99

Case#3: PostgreSQL MAX() function with ‘GROUP BY’ clause

The following code will demonstrate the maximum value from the “productprice” column and group the results by sno(1001,1002,1003,1004).

SELECT MAX(DISTINCT productprice) AS sno, productprice FROM products GROUP BY sno;
—--OUTPUT—--
+----------+----------------------------------------+
| sno | productprice |
+----------+----------------------------------------+
| 1001 | 250.99 |
| 1003 | 245.00 |
| 1004 | 19.99 |
+----------+----------------------------------------+

Note: The MAX() function ignores any NULL values present in the table.

Case#4: PostgreSQL MAX() function with Sub-Query

• Using WHERE clause: This query will fetch the record for the highest ‘productprice’ from the records where the sno=’1001.’

SELECT MAX(productprice) 
FROM products
WHERE sno = 1001;
—--OUTPUT—--
250.99

• Using the HAVING clause: Finally, let’s look at the usage of the HAVING clause with the PostgreSQL MAX() function.

SELECT sno, MAX(productprice) 
FROM products
GROUP BY sno
HAVING MAX(product)>=200;
—--OUTPUT—--
+----------+----------------------------------------+
| sno | productprice |
+----------+----------------------------------------+
| 1001 | 250.99 |
| 1003 | 245.00 |
+----------+----------------------------------------+

Conclusion

This article demonstrates how PostgreSQL MAX() function works, retrieve largest value from a set of values. The tutorial also discussed applying the PostgreSQL MAX() function to specify an expression in a set of grouped rows and how it can also be used in a subquery with the WHERE and HAVING clauses. 

Several practical examples of the PostgreSQL MAX() function, including the use of the ‘GROUP BY’ clause and selecting the maximum value by Integer, String and Date, were discussed in the article; which you can refer to in your journey of learning!

Also, check out these amazing articles at Hevo:

• Working with Postgres JSON Query | Made Easy
• How PostgreSQL date_part works? | Syntax & 7 Use Cases Simplified

If you’re struggling with PostgreSQL, copying data into a warehouse using ETL for Data Analysis might be an extensive task.

Hevo, a No-Code Data Pipeline, is at your service for your rescue. You can save your engineering efforts by setting up a Data Pipeline and start replicating your data from PostgreSQL to your desired warehouse in a matter of minutes using Hevo.

Visit our Website to Explore Hevo

Want to give Hevo a try? for a 14-day free trial and experience the feature-rich Hevo suite first hand.

Share your experience of understanding the PostgreSQL MAX() function in the comment section below! We would love to hear your thoughts.