CODEWITHSUNDEEP
sql-server
Dogukan Evcil
Dogukan Evcil

Reputation: 373

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

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?

Upvotes: 1

Views: 12279

Answers (2)

Thom A
Thom A

Reputation: 95949

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

Upvotes: 5

Peter B
Peter B

Reputation: 24280

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

Upvotes: 0

Related Questions