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.
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
