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
精彩评论