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

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.

Recommended Courses

1. SQL for Data Science – Level 1
2. SQL for Data Science – Level 2
3. SQL for Data Science – Level 3

Recommended Tutorial

1. Introduction to SQL
2. SQL Window Functons – Made Simple and Easy
3. SQL Subquery

More SQL Questions

1. How to access previous row value in SQL?
2. How to exclude specific columns using select except?
3. How to transpose columns to rows in SQL?
4. How to select first row in each GROUP BY group?