Oracle : select maximum value from different columns of the same row

The whole question is prett开发者_高级运维y much in the title. For each row of the table I'd like to select the maximum of a subset of columns.

For example, from this table

name m1 m2 m3 m4
A    1  2  3  4
B    6  3  4  5
C    1  5  2  1

the result would be

name max
A    4
B    6
C    5

The query must be compatible oracle 8i.

Given this test data ...

SQL> select *
  2  from your_table
  3  /

NAME         M1         M2         M3         M4
---- ---------- ---------- ---------- ----------
A             1          2          3          4
B             6          3          4          5
C             1          5          2          1

SQL>

... a straightforward GREATEST() call will give the desired result:

SQL> select name
  2          , greatest(m1, m2, m3, m4) as the greatest_m
  3  from your_table
  4  /

NAME THE_GREATEST_M
---- --------------
A                 4
B                 6
C                 5

SQL>

Note that greatest() will return NULL if any of the arguments are null. If this is a problem then use nvl() to provide a default value which won't distort the outcome. For instance, if no values can be negative....

SQL> select name
  2          , greatest(nvl(m1,0), nvl(m2,0), nvl(m3,0), nvl(m4,0)) as the greatest_m
  3  from your_table
  4  /

NAME THE_GREATEST_M
---- --------------
A                 4
B                 6
C                 5

SQL>

Use GREATEST but also handle possible NULL's

SELECT name, GREATEST(NVL(m1,0), NVL(m2,0), NVL(m3,0), NVL(m4,0)) AS "Max"
FROM yourtable

Input:

name m1 m2 m3 m4
A    1  2  3  4
B    6  3  4  5
C    1  5  2  1

Output:

NAME Max
A    4
B    6
C    5

SQL Fiddle: http://sqlfiddle.com/#!4/ae268/7/0

Input:

name m1 m2   m3 m4
A    1  2    3  null
B    6  null 4  5
C    1  5    2  1

Output:

NAME Max
A    3
B    6
C    5

SQL Fiddle: http://sqlfiddle.com/#!4/b1c46/1/0