CODEWITHSUNDEEP
sqloracle-databaseoracle8i
gregseth
gregseth

Reputation: 13408

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

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.

Upvotes: 47

Views: 102105

Answers (2)

APC
APC

Reputation: 146179

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>

Upvotes: 103

Matt
Matt

Reputation: 15061

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

Upvotes: 24

Related Questions