Deepesh Deepesh - 4 months ago 20
SQL Question

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.

Answer

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