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.