CODEWITHSUNDEEP
mysqlsqlmaxrows
Deepesh
Deepesh

Reputation: 840

Fetching the max value row wise in mysql

I have a table structure 

ID  Col_1  col_2  col_3  col_4
1    34     23     45     32
2    20     19     67     18
3    40     10     76     86

I here want the max value from col_1,col_,col_3,col_4 so my output looks like

 ID   Col_1   col_2  col_3  col_4  max
    1    34     23     45     32   45
    2    20     19     67     18   67 
    3    40     10     76     86   86

I tried using 

SELECT ID, MAX(col_1,col_2,col_3,col_4) as max
FROM demo
GROUP BY ID

any help would be much appreciated.

Upvotes: 0

Views: 511

Answers (4)

Deepesh
Deepesh

Reputation: 840

@Thijs to your reference this is the corresponding output I got.

enter image description here

query result of first query by @Madhivanan

enter image description here

query result from first of the two solutions provided

Upvotes: 0

ashish mulani
ashish mulani

Reputation: 197

you may be try this 

SELECT ID, col_1, col_2, col_3, col_4, 
GREATEST(col_1, col_2, col_3, col_4) AS max_value FROM table_name

Upvotes: 1

realnumber3012
realnumber3012

Reputation: 1062

u can use mysql function GREATEST

SELECT id, col1, col2, col3, col4, 
GREATEST(col1, col2, col3, col4) AS mx FROM demo

http://sqlfiddle.com/#!9/9cbb0/2

Upvotes: 1

Madhivanan
Madhivanan

Reputation: 13700

You need to normalize the table structure. Try this

select ID, max(Col_1) as max_value from
(
select ID, Col_1 from table
union all
select ID, Col_2 from table
union all
select ID, Col_3 from table
union all
select ID, Col_4 from table
) as t group by ID

Upvotes: 2

Related Questions