PostgreSQL min() Function

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

min() Syntax

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

min(expr)

Typically, we use the min() function like:

SELECT min(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 min() function returns the minimum value among all specified values ​​in a group.

min() Examples

To demonstrate usages of the PostgreSQL min() 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 to get the worst grade of all grades, use the following statement with the min() function:

SELECT
  min(t.grade) min_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;

 min_grade
-----------
         5
(1 row)

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

SELECT
  t.name,
  min(t.grade) min_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 | min_grade
------+-----------
 Tim  |         8
 Tom  |         5
(2 rows)

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