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?
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))
A small change will do: give the UNION ALL result a name, and then query from it. I used the name union_result, you can of course pick just about any name you like.
SELECT MAX(union_result.value)
FROM (SELECT LEN('tests') as value
UNION ALL
SELECT LEN('test') as value
) AS union_result
