Often in PostgreSQL you may want to find the max value across multiple columns.
You can use the following syntax to do so:
SELECT *,
GREATEST(points_g1, points_g2, points_g3) AS max_points
FROM athletes;
This particular example will find the max value across the columns named points_g1, points_g2 and points_g3 of the table named athletes.
The max value will be saved to a new column named max_points.
Note that we use the GREATEST statement to find the max value across several specific columns.
The following example shows how to use this syntax in practice.
Example: How to Find Max 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 max points scored by each player by calculating the max across the points_g1, points_g2 and points_g3 columns.
We can use the following syntax to do so:
SELECT *,
GREATEST(points_g1, points_g2, points_g3) AS max_points
FROM athletes;
athleteid | points_g1 | points_g2 | points_g3 | max_points
-----------+-----------+-----------+-----------+------------
1 | 22 | 5 | 8 | 22
2 | 30 | 12 | 10 | 30
3 | 12 | 3 | 6 | 12
4 | 25 | 9 | 34 | 34
5 | 31 | 14 | 18 | 31
6 | 16 | 3 | 7 | 16
7 | 10 | 5 | 9 | 10
8 | 14 | 13 | 12 | 14
9 | 20 | 8 | 12 | 20
10 | 26 | 3 | 20 | 26
The new column named max_points contains the max number of points scored by each player across the three games.
For example, we can see:
- The max points scored by the first player was 22.
- The max points scored by the second player was 30.
- The max points scored by the third player was 12.
And so on.
Feel free to use similar syntax to find the max 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
