PostgreSQL max() Function

The PostgreSQL max() function is an aggregate function that returns the maximum value of all specified values ​​in a group.

max() Syntax

Here is the syntax of the PostgreSQL max() function:

max(expr)

Typically, we use the max() function like:

SELECT max(expr), ...
FROM table_name
[WHERE ...]
[GROUP BY group_expr1, group_expr2, ...];

Parameters

expr

Required. A column name or expression that computes the sum.

Return value

The PostgreSQL max() function returns the maximum value of all specified values ​​in a group.

max() Examples

To demonstrate usages of the PostgreSQL max() function, we simulate a temporary table using the following statement with UNION and SELECT:

SELECT 'Tim' name, 'Math' subject, 8 grade
UNION
SELECT 'Tim' name, 'English' subject, 9 grade
UNION
SELECT 'Tom' name, 'Math' subject, 7 grade
UNION
SELECT 'Tom' name, 'English' subject, 5 grade;

 name | subject | grade
------+---------+-------
 Tim  | English |     9
 Tom  | Math    |     7
 Tim  | Math    |     8
 Tom  | English |     5
(4 rows)

Here, we have some rows for user grades. The name column is the name of the user, the subject column is the name of the subject, and the grade column is the grade of a subject.

If you want the best of all grades, use the following statement with the max() function:

SELECT
  max(t.grade) max_grade
FROM
  (
    SELECT 'Tim' name, 'Math' subject, 8 grade
    UNION
    SELECT 'Tim' name, 'English' subject, 9 grade
    UNION
    SELECT 'Tom' name, 'Math' subject, 7 grade
    UNION
    SELECT 'Tom' name, 'English' subject, 5 grade
  ) t;

 max_grade
-----------
         9
(1 row)

If you want to get best grade for everyone, use the following statement with the max() function:

SELECT
  t.name,
  max(t.grade) max_grade
FROM
  (
    SELECT 'Tim' name, 'Math' subject, 8 grade
    UNION
    SELECT 'Tim' name, 'English' subject, 9 grade
    UNION
    SELECT 'Tom' name, 'Math' subject, 7 grade
    UNION
    SELECT 'Tom' name, 'English' subject, 5 grade
  ) t
GROUP BY t.name;

 name | max_grade
------+-----------
 Tim  |         9
 Tom  |         7
(2 rows)

Here, we use the GROUP BY t.name clause to group all rows by the user’s name, and use the max(t.grade) function to get the best grade in each group.