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 col3
If 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 function—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.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback, SQL Consultant, r937.com
Read an example of an SQL case expression from our SQL expert Rudy Limeback.continue reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback.continue reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clausecontinue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.