Q

The MAX of two MAX values in SQL

Can I get the max of two columns into one column in SQL?

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.

This was first published in June 2007

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

1 comment

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close