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

Question

The whole question is pretty 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.

Tarr

Answer 1

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>

Hypsometer

Just a warning that, if any of the columns is NULL, greatest will always return NULL – Sweepstake

Answer 2

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

Schopenhauer