Can I get the max of two columns into one column as in the following example:
max( max(col1) from table1),
max(col2) from table2) ) as col3If yes, what is the command to use in place of max?
The answer to your question is the GREATEST function. However, we must make a couple of small changes to your expressions to make the query work.
select greatest(
( select max(col1) from table1 )
, ( select max(col2) from table2 )
) as col3 Here each subquery produces a single value, and then GREATEST, which is a scalar function—as opposed to MAX, which is an aggregate fun
Requires Membership to View
To gain access to this and all member only content, please provide the following information:
By joining SearchOracle.com you agree to receive email updates from the TechTarget network of sites, including updates on new content, magazine or event notifications, new site launches and market research surveys. Please verify all information and selections above. You may unsubscribe at any time from one or more of the services you have selected by editing your profile or unsubscribing via email.
TechTarget cares about your privacy. Read our Privacy Policy
ction—selects the higher of those values.
If your particular database system does not support the standard SQL GREATEST function, use this:
select case when max1 > max2
then max1
else max2
end as col3
from ( select max(col1) as max1
from table1
) as t1
cross
join ( select max(col2) as max2
from table2
) as t2 Why a cross join? Because each of the derived tables (t1 and t2) returns only one row, so the cross join produces only one row, with two columns, max1 and max2.
Note that GREATEST can take any number of values. The CASE approach, however, becomes complex and cumbersome for more than two values.
Oracle White Papers: Fusion Middleware