How to Find the Maximum of Multiple Columns in PostgreSQL?

PostgreSQL is one of the most advanced general-purpose object-relational database management systems and is open-source. Being an open-source software, its source code is available under PostgreSQL license, a liberal open-source license. Anyone with the right skills is free to use, modify, and distribute PostgreSQL in any form. As it is highly stable, very low effort is required to maintain this DBMS. In this article, we will explore how one can find the maximum of multiple columns in PostgreSQL.

Finding the Maximum Value Among Multiple Columns in PostgreSQL

Before we delve deeper into the queries, let us start by creating the table and inserting some sample values in the table. The following code creates the Sample Table and inserts four entries in the table.

CREATE TABLE SampleTable (
    id INT PRIMARY KEY,
    column1 INT,
    column2 INT
);

INSERT INTO SampleTable (id, column1, column2)
VALUES
    (1, 10, 20),
    (2, 25, 15),
    (3, 5, 40),
    (4, 30, 10);

The following is the current data in the table:

SampleTable

We are going to have a look at two methods in this article to go about finding the maximum of multiple columns of the table.

Method 1: Using CASE expression

The CASE expression allows the user to write conditions much like if-else or switch statements in PostgreSQL. We can use this to create condition when one column is greater than every other.

The following query does the trick to find the maximum of two columns:

SELECT
    id,
    column1,
    column2,
    CASE
      WHEN column1 > column2 THEN column1
      ELSE column2
    END AS max_value
FROM
    SampleTable;

Output:

Using CASE expression

Explanation: As you can already see if the number of columns from which we have to compare increases, the code becomes very complicated and cumbersome. The next method solves this issue.

Method 2: Using GREATEST() Function

The GREATEST() function returns the maximum value of all the arguments with the number of arguments can be anything. So using the GREATEST() function we can compare literal values as well as columns.

The following query compares the two columns that we have and returns the result as before:

SELECT
    id,
    column1,
    column2,
    GREATEST(column1, column2) AS max_value
FROM
    SampleTable;

Output:

Using GREATEST Function

Example of Find the Maximum of Multiple Columns

Let’s now use the concepts we have learned in this article in a technical example.

First, let’s create the table and insert some data inside it. The following query creates a sales table and inserts three records in it.

-- create
CREATE TABLE SALES (
  product_name VARCHAR(20),
  jan INT,
  feb INT,
  mar INT
);

-- insert
INSERT INTO SALES VALUES ('Book', 123, 89, 22);
INSERT INTO SALES VALUES ('Pen', 99, 12, 51);
INSERT INTO SALES VALUES ('Sharpner', 82, 47, 90);

The above table contains the information about different products and the number of units sold in January, February, and March. So, the record (‘Book’, 123, 89, 22) states that 123 units, 89 units, and 22 units of the book was sold in January, February, and March respectively.

The following query returns the initial data in the table:

SELECT * FROM SALES;

Output:

Initial data

Now let’s try to find out what the maximum unit of a product sold in any of the three months for all the products in the table. For this we are going to make use of the GREATEST() function we understood in method 2. As already mentioned, we can use GREATEST() function to find maximum value from more than 2 values. The following query makes use of GREATEST() function to find the maximum units sold for each product:

SELECT
    product_name,
    GREATEST(jan, feb, mar) AS max_sales
FROM
    SALES;

Output:

Query output

As you can see that books were sold maximum number of 123 units, pens were sold maximum number of 99 units, and sharpeners were sold maximum number of 90 units.

Conclusion

In this article, we covered how we can find the maximum of multiple columns in PostgreSQL. We had a chance to look at two different methods to go about doing this, first using CASE statement. We understood the pitfalls of the CASE statement and how quickly it can get very complicated. We later looked at the GREATEST() function and understood the ease it provides. We also how we can use the concepts we learned in this article to a real-life situation through the technical example.