Max Value across several columns in a row

Analytics
Enthusiast

Max Value across several columns in a row

Hi ,

I have a problem. For every row in a table , i have to find out max across 5 columns. Though i can use Case statement, I want to know the most efficient way of doing this.

For eg-

id data1 data2 data3 data4 datat5
e1 4 5 6 1 0
e2 2 8 7 9 2

the result set should look like

id max_value
e1 5
e2 9

Any help on this will be very much appreciated.

Thanks
Angel
2 REPLIES
Teradata Employee

Re: Max Value across several columns in a row

Hello,

CASE itself is pretty efficient, isn't it?

Regards,

Adeel
SN
Enthusiast

Re: Max Value across several columns in a row

though late...just wanted to share this...

try to rearrange the data into a new table as below:

id data
e1 4
e1 5
e1 6
e1 10
e2 2
e2 8
e2 7
e2 9
e2 2

use the MAX fn and group by on id.

thx