How to compute maximum of multiple columns, aks row wise max?

By Rajesh Vaddi · October 2023

Problem

Given a table with multiple columns, compute the maximum value for each row across the specified columns.

Input

-- Create an example table
CREATE TABLE ExampleTable (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    ColumnA INT,
    ColumnB INT,
    ColumnC INT
);

-- Insert data into the table
INSERT INTO ExampleTable (ColumnA, ColumnB, ColumnC)
VALUES
    (10, 20, 30),
    (25, 15, 35),
    (40, 10, 5),
    (30, 30, 30),
    (5, 45, 20);

ExampleTable:

ID	ColumnA	ColumnB	ColumnC
1	10	20	30
2	25	15	35
3	40	10	5
4	30	30	30
5	5	45	20

Desired Solution

ID	RowMax
1	30
2	35
3	40
4	30
5	45

Solution:

To find the maximum value for each row across multiple columns in MySQL, you can use the GREATEST function.

SELECT ID,
       GREATEST(ColumnA, ColumnB, ColumnC) AS RowMax
FROM ExampleTable;

Output:

ID	RowMax
1	30
2	35
3	40
4	30
5	45

Explanation:

The above SQL query will return the maximum value for each row across ColumnA, ColumnB, and ColumnC.

In MySQL, the GREATEST function is used to return the greatest value in a list of expressions. It can compare numbers, strings, dates, etc., and will return the highest value among the given list.

For example, the expression GREATEST(10, 20, 30) will return 30 as it’s the largest number in the provided list.