1

I have a table with three integer columns:

select max(col1), max(col2), max(col3) from mytable

I want the max of both three columns. But this does not work:

select max(max(col1), max(col2), max(col3)) from mytable

How to get this done?

I use PostgreSQL, but I hope that this can be solved with standard SQL.

6

That should be GREATEST(), not MAX(). MAX() is for aggregating over values from rows, GREATEST() is for aggregating over a list of values (from columns, expressions).

You can either find the 3 max values, one for each column and then the greatest of the three:

select greatest(max(col1), max(col2), max(col3)) 
from mytable ;

or find the greatest value in each row and then the max over all of them:

select max(greatest(col1, col2, col3)) 
from mytable ;
1

In our MS SQL Server 2008 database we have several levels of prices for products and I use something like this to get MAX and MIN prices for each product:

select price_1, price_2, price_3, price_4,  price_6, max_price
    from prices
        CROSS APPLY (SELECT MAX(d) max_price FROM (VALUES (price_1),(price_2),(price_3),(price_4),(price_6)) AS a(d)) A

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.