Often in PostgreSQL you may want to find the minimum value across multiple columns.
You can use the following syntax to do so:
SELECT *,
LEAST(points_g1, points_g2, points_g3) AS min_points
FROM athletes;
This particular example will find the minimum value across the columns named points_g1, points_g2 and points_g3 of the table named athletes.
The minimum value will be saved to a new column named min_points.
Note that we use the LEAST statement to find the minimum value across several specific columns.
The following example shows how to use this syntax in practice.
Example: How to Find Minimum Value Across Columns in PostgreSQL
Suppose that we create the following table named athletes that contains information about the points scored during three consecutive games by various basketball players:
-- create table named 'athletes'
CREATE TABLE athletes (
athleteID INTEGER PRIMARY KEY,
points_g1 INTEGER,
points_g2 INTEGER,
points_g3 INTEGER
);
-- add rows to the table
INSERT INTO athletes VALUES (0001, 22, 5, 8);
INSERT INTO athletes VALUES (0002, 30, 12, 10);
INSERT INTO athletes VALUES (0003, 12, 3, 6);
INSERT INTO athletes VALUES (0004, 25, 9, 34);
INSERT INTO athletes VALUES (0005, 31, 14, 18);
INSERT INTO athletes VALUES (0006, 16, 3, 7);
INSERT INTO athletes VALUES (0007, 10, 5, 9);
INSERT INTO athletes VALUES (0008, 14, 13, 12);
INSERT INTO athletes VALUES (0009, 20, 8, 12);
INSERT INTO athletes VALUES (0010, 26, 3, 20);
-- view all rows in table
SELECT * FROM athletes;
athleteid | points_g1 | points_g2 | points_g3
-----------+-----------+-----------+-----------
1 | 22 | 5 | 8
2 | 30 | 12 | 10
3 | 12 | 3 | 6
4 | 25 | 9 | 34
5 | 31 | 14 | 18
6 | 16 | 3 | 7
7 | 10 | 5 | 9
8 | 14 | 13 | 12
9 | 20 | 8 | 12
10 | 26 | 3 | 20
Suppose that we would like to find the minimum points scored by each player by calculating the minimum across the points_g1, points_g2 and points_g3 columns.
We can use the following syntax to do so:
SELECT *,
LEAST(points_g1, points_g2, points_g3) AS min_points
FROM athletes;
athleteid | points_g1 | points_g2 | points_g3 | min_points
-----------+-----------+-----------+-----------+------------
1 | 22 | 5 | 8 | 5
2 | 30 | 12 | 10 | 10
3 | 12 | 3 | 6 | 3
4 | 25 | 9 | 34 | 9
5 | 31 | 14 | 18 | 14
6 | 16 | 3 | 7 | 3
7 | 10 | 5 | 9 | 5
8 | 14 | 13 | 12 | 12
9 | 20 | 8 | 12 | 8
10 | 26 | 3 | 20 | 3
The new column named min_points contains the minimum number of points scored by each player across the three games.
For example, we can see:
- The minimum points scored by the first player was 5.
- The minimum points scored by the second player was 10.
- The minimum points scored by the third player was 3.
And so on.
Feel free to use similar syntax to find the minimum value across multiple columns in your own table.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Calculate a Moving Average
PostgreSQL: How to Round Numbers to 2 Decimal Places
PostgreSQL: Count Occurrences of Each Value in Column
