How to Use the PostgreSQL MAX Function Part 2
Introduction
When you’re querying data in PostgreSQL, you may want to identify and retrieve the maximum value from a set of values. The MAX function is a PostgreSQL aggregate function that makes it easy to get the job done. In the first installment of this two-part article series, we provided an introduction to the PostgreSQL MAX function. Now, we’ll pick up where we left off and continue our discussion of this function, providing several examples of its use.
Prerequisites
Before continuing with this second article in the series, be sure that you’ve read and followed along with the first installment. In the first article, we discussed the requirements for this tutorial, created some sample data and stored it in a table to use in our examples of the MAX function.
PostgreSQL MAX Function Examples
In the following examples, we’ll use a table named employees. Here’s the contents of the table:
1 2 3 4 5 6 7 8 9 10 | 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 (7 ROWS) |
Use the MAX Function in Two Columns
The MAX function can be used multiple times in a single SELECT statement. For example, let’s get the most recent employment date for an employee and the oldest age of the employees:
1 | SELECT MAX(employment_date) AS most_recent_employed, MAX(age) AS oldest_age FROM employees; |
The output should look like this:
1 2 3 4 | most_recent_employed | oldest_age ----------------------+------------ 2019-12-12 | 37 (1 row) |
MAX Function with ‘GROUP BY’ Clause
The PostgreSQL MAX function can also be used in conjunction with the GROUP BY clause. By utilizing the GROUP BY clause, we can select all the ages of the employees and remove duplicates. Let’s look at the following example:
1 2 3 4 5 6 7 8 9 10 | SELECT MAX(age) AS all_age FROM employees GROUP BY age ORDER BY age DESC; MAX ----- 37 34 33 32 30 28 (6 ROWS) |
Max function with ‘HAVING’ clause
In our next example, we’ll select the oldest age from the employees and group them by gender. We’ll use the HAVING clause, which allows us to filter our results– if the age is less than 35, the maximum age of that gender will not be displayed. This may sound complex, but it’s quite straightforward when you see the clause being used in a SELECT statement. Let’s look at the example below:
1 | SELECT MAX(age) AS maximum_age, gender FROM employees GROUP BY gender HAVING MAX(age) > 35; |
The result set consists of one row and will look like the following:
1 2 3 4 | maximum_age | gender -------------+-------- 37 | Female (1 row) |
Let’s look at another example of the HAVING clause. This time, we’ll return the list of employees that have an age greater than 30. We’ll sort the results by age in descending order:
1 2 3 4 5 6 7 8 9 | SELECT * FROM employees GROUP BY id HAVING MAX(AGE) > 30 ORDER BY MAX(age) DESC; id | name | gender | employment_date | age ----+------------------+--------+-----------------+----- 5 | Eleanor Harmon | Female | 2019-12-12 | 37 4 | Willard Anderson | Male | 2018-06-02 | 34 6 | Jade Bowman | Female | 2019-10-29 | 34 7 | Bradley White | Male | 2018-03-13 | 33 1 | Erick Seward | Male | 2019-05-19 | 32 (5 ROWS) |
You can see that the results are both filtered by the HAVING clause and sorted by the ORDER BY clause.
Conclusion
When you’re working with data in PostgreSQL, there may be many situations where you need to find the maximum value of a set of data. Whether you’re looking for the highest-priced product in your inventory, the top grade in a class or the oldest employee in an organization, the PostgreSQL MAX function can help you get the information you need. In this article, we looked at several examples of the MAX function. With these helpful examples to guide you, you’ll be ready to incorporate the MAX function into your own database queries.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started