How to find the max of each row in a table in MYSQL

Question

I have this table below which has N rows. here i simplify it to 3 rows.

Example Table

        A                   B                   C                      D
5.640700669769904   5.623475843268976   5.644739934022418
5.62489798487818    5.581388826442135   5.62376476667017
5.62708139972593    5.606610562903928   5.592366625377977

i need to find the new column D which consists of the maximum of the row for columns A,B,C.

in MYSQL MAX(X) only applies to the column. How would i solve this problem?

I have tried with sub tables but no luck.

"I have tried with sub tables but no luck" So where is it???? I don't see any code. — Eric, Apr 23 '20 at 18:07
This kind of problem is highly symptomatic of poor or flawed schema design — Strawberry, Apr 23 '20 at 21:26
Interesting. Can you elaborate on this? I am not sure I understand what you mean by that. The original columns A, B, C cannot be collapse into one initially. — AndiAna, Apr 24 '20 at 04:41

score 3 · Accepted Answer · answered Apr 23 '20 at 18:00

max() is an aggregate functions that operates over values that are store across rows (although some databases, namely SQLite, allows using it for list of values).

You can use greatest() to get the greatest value over the three columns on each row:

select t.*, greatest(a, b, c) d from mytable

On the other hand, if you want the greatest value over all 3 columns and all rows, you can use both max() and greatest() together:

select max(greatest(a, b, c)) overall_greatest from mytable

How to find the max of each row in a table in MYSQL

1 Answers1