CODEWITHSUNDEEP
mysql
WitchOfCloud
WitchOfCloud

Reputation: 115

Mysql get maximum value in one tuple

I make a table from row data like:

+----------------------+
| id | 1st | 2nd | 3th |
+----------------------+
|  0 |   1 |   3 |  2.5|
|  1 | 2.5 |   5 |    3|
|  2 |   4 | 3.5 |    0|
+----------------------+

And I want get this:

+--------------+
| id | max set | 
+--------------+
|  0 |     2nd | 
|  1 |     2nd |  
|  2 |     1st | 
+--------------+

How can I do?

Upvotes: 0

Views: 38

Answers (2)

user3741598
user3741598

Reputation: 295

Tested - use CASE: select lineno,
case when 1st>2nd and 2nd>3rd then '1st' when 2nd>1st and 2nd>3rd then '2nd' when 3rd>1st and 3rd>2nd then '3rd' else 0 end as 'MaxValue' from yourtable;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270713

You can do this using greatest() and field():

select id,
       field(greatest(col1, col2, col3), col1, col2, col3) as ColWithMax
from table t;

Upvotes: 2

Related Questions