How to Find the Maximum of Multiple Columns in SQL

Finding the maximum value of multiple columns is one of the most common analytical tasks essential for making decisions and analyzing data. Using the MAX() function of SQL, users can find the maximum value in a single column. But to find the maximum value in multiple columns, users need to use other SQL statements like CASE or GREATEST.

This article covers the methods to find the max value of multiple columns in a SQL table.

Demo SQL Database

Below is a demo SQL table that will be used in examples later in the article.

id	column1	column2
1	10	20
2	25	15
3	5	40
4	30	10

To create this table, write the following SQL queries:

SQL

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);

Finding the Maximum Value Among Multiple Columns in SQL

There are two methods in SQL to find the maximum value of multiple columns in SQL:

Let’s study each of these methods in detail.

Using CASE expression to Find Max Value of Multiple Columns in SQL

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

Example

Finding maximum of two columns in SQL:

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.

Using GREATEST() Function to Find Max Value of Multiple Columns in SQL

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.

Example

Finding max of two columns in SQL using GREATEST function

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

Output:

Output

Conclusion

In this article, we covered how to find the maximum of multiple columns in SQL. There are two different methods to do this, first using CASE statement and second the GREATEST function.

GREATEST function is a better option as CASE statement is a more complex approach to find max value of multiple columns. Both the methods are explained with examples for better understanding.