How do I get the MAX of two values in SQL Server?

Question

I am trying to get the max number of two numbers and I figured that I cannot do it like this SELECT MAX(2, 4).

I did try to do it like this but got an error. Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

SELECT  MAX( (SELECT LEN('tests') as value
            UNION ALL
            SELECT LEN('test') as value) );

How can I overcome this or achieve what I want?

Answer 1

In SQL Server 2022, and in Azure releases, you have the function GREATEST which achieves what you want. GREATEST(2,4) would return 4. The function accepts 2+ paramters.

Otherwise no, you can't do MAX(2,4); MAX only expects one parameter.

For something simple like this, you can use a CASE expression. For example:

SELECT CASE WHEN A > B THEN A ELSE B END

Note this assumes neither value can be NULL. If they can be, then would do something like this:

SELECT CASE WHEN B IS NULL OR A > B THEN A
            ELSE B
       END            

For more complex scenarios, you can use a subquery to unpivot the data:

SELECT (SELECT MAX(V.V)
        FROM(VALUES(A),(B),(C),(D),(E),(F),(G))V(V))

db<>fiddle