How to Use the PostgreSQL Max Function Part 1

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

The PostgreSQL MAX function is used for obtaining the maximum value from a set of values and can be used to specify an expression in a set of amassed, or aggregated, rows. The max function accepts an expression of any numeric data type, including string, date and time values and then returns the maximum value of the same data type matching the type that was specified in the expression. This is part one of a tutorial series that will cover how to use the PostgreSQL max function to obtain the maximum value from a set of values.

Prerequisites

  • PostgreSQL must be properly installed and configured on the local machine to execute the examples for the PostgreSQL MAX function covered in this tutorial.

  • A basic working knowledge of PostgreSQL is required to execute the examples used in this tutorial.

PostgreSQL max function

The PostgreSQL max function is used to specify an expression in a set of amassed rows. The function obtains expressions like a string, time and numeric data type and displays the maximum value of the data type that is of the same data type value used in the expression.

Following is the syntax for using the MAX function in PostgreSQL:

1
SELECT MAX (COLUMN_NAME) FROM TABLE_NAME;

The MAX function can also be used with the WHERE and HAVING clauses. This function also accepts the values of different data types, such as string, integer, date and time.

PostgreSQL max function examples

The subsequent examples will use the following table named employees:

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)

The following example demonstrates how to get the highest value from the column id:

1
2
3
4
5
SELECT MAX(id) FROM employees;
 MAX
-----
   7
(1 ROW)

Max function with ‘GROUP BY’ clause

The following example will select the maximum value from the “name” column and will then group the results by gender:

1
2
3
4
5
6
SELECT MAX(name) AS name, gender FROM employees GROUP BY gender;
       name       | gender
------------------+--------
 Olivia Lawson    | Female
 Willard Anderson | Male
(2 ROWS)

Max function with sub query

The MAX function can also be used in a sub query as shown in the next section.

Selecting the maximum value by integer

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

1
2
3
4
5
SELECT * FROM employees WHERE id = (SELECT MAX(id) FROM employees);
 id |     name      | gender | employment_date | age
----+---------------+--------+-----------------+-----
  7 | Bradley White | Male   | 2018-03-13      |  33
(1 ROW)

Note that the MAX function is designed to select the highest integer from the records.

Selecting the maximum value by string

This example shows how to select a record by getting the maximum values from the name column:

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

Note that when used on a string, the MAX function will look for the first character of the string that is nearest to the end of the alphabet.

Selecting the maximum value by date

The following example demonstrates how to select a record by getting the maximum values from the employment_date column:

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

Note here that the returned value of the MAX function will be the most recent date.

Conclusion

This was part one of a tutorial series explaining how to use the PostgreSQL max function to obtain the maximum value from a set of values. The tutorial explained how the PostgreSQL max function is used to specify an expression in a set of amassed rows and how it can also be used in a sub query with the WHERE and HAVING clauses. The article then provided several PostgreSQL max function examples including the use of the ‘GROUP BY’ clause and selecting the maximum value by integer, string and date. The tutorial also demonstrated how to select a record by obtaining the maximum values from the ID column. Remember that when using the Max function on a string it will look for the first character of the string that is nearest to the end of the alphabet.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.