Menu
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.
250
If you want to understand VALUE try this query which creates a virtual 1 column table: SELECT * FROM (VALUES (1), (5), (1)) as listOfValues(columnName) And this query which creates a virtual 2 column table: SELECT * FROM (VALUES (1,2), (5,3), (1,4)) as tableOfValues(columnName1, ColumnName2) Now you can understand why ...
A utility table in Oracle with only 1 row and 1 column. It is used to perform a number of arithmetic operations and can be used generally where one needs to generate a known output. SELECT * FROM dual; will give a single row, with a single column named "DUMMY" and a value of "X" as shown here: DUMMY ----- X.
The LIMIT clause is used to specify the maximum number M of results to return to the application. M is computed by an expression that may be a single integer literal, or a single external variable, or any expression which is built from literals and external variables and returns a single non-negative integer.
The SQL MAX function is used to return the maximum value of an expression in a SELECT statement.
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
32
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
