SQL Server Max Function Takes Two Values Like Math.Max in .NET

In SQL Server 2022, the GREATEST() function simplifies finding the maximum value among a set of parameters. However, for older versions without this function, we explore alternative approaches akin to the Math.Max() function in .NET. This article presents the syntax of GREATEST() and introduces three alternative methods: using a User-Defined Function (UDF), employing the CASE statement, and leveraging the IIF function.

GREATEST()

In SQL Server 2022, the GREATEST() function efficiently retrieves the maximum value from a set of expressions. The syntax is as follows:

–Syntax of the Greatest()

GREATEST ( expression1 [ , …expressionN ] )

Here in this function, we have to give at least one value and after that, we can give as much as values as we want. SQL server will compare them and then the maximum value from them is returned.

If you don’t have SQL Server 2022 then you can also use Azure Data Studio.

Example Usage

So, let’s write a query that returns the maximum from two integers.

SELECT GREATEST(1.2,2,3,4) AS Maximum;

Output:

Result of the query

Other Approaches for Older Versions

If you have the older versions then you cannot use the in-built function and for that, you have to use other approaches. There are different ways present but here we are going to discuss the approaches listed below.

1. Using UDF
2. Using CASE
3. Using IIF

Before moving to this approach, we have to look at one problem.

While writing any user-defined approach we will be in a dilemma of using the data type because we cannot write the same logic for every different datatype. And for that, we are going to use “sql_variant”, this will help us to avoid the above problem because this will be ok with every datatype.

1. Using User-Defined Function

So, in UDF we will create a simple function that will take two input parameters and one will be the output parameter which is the result variable. In the body, we will compare the variables and return the maximum value. For comparison, we will use the if statement.

CREATE FUNCTION Maxi(@var1 sql_variant, @var2 sql_variant)

RETURNS sql_variant

AS

BEGIN

IF @var1 > @var2

RETURN @var1

RETURN isnull(@var2,@var1)

END

Go

Now we can use this function for finding the max just like Math.Max() in .NET.

Declare @var1 float = 1.2;
Declare @var2 float = 2.2;

select [dbo].Maxi(@var1, @var2)
go

Output:

UDF Output

Explanation: The declared variables @var1 and @var2 have values 1.2 and 2.2, respectively. The SELECT statement calls the user-defined function [dbo].Maxi(), comparing the variables and returning the maximum value, which is 2.2 in this case.

2. Using Case

In this case, we can give the expression in the when clause and the output, or the maximum value can be given with the then and else statement.

Declare @var1 float = 1.2;
Declare @var2 float = 2.2;

select case when @var1 > @var1
then @var1
else @var2
end

Output:

Using case

Using this we can compare many variables with different cases. But only the problem with this is that as the no. of variable increases the size of the query will increase and also we have to write so many clauses to get one outcome.

3. Using IIF

IIF comes with three parameters, first, the expression will give true or false, and based on that other two parameters are being used. For example, if the expression returns true then the first variable will be returned and if it returns false then the third variable will be given as output.

Syntax:

SELECT IIF (condition, true_value, false_value);

Example

SELECT IIF(1>2,1,2)

Output:

This is the simplest way of getting the maximum number if the count is 2. But again if the no. of variables is more than two then the process of finding would be tough.

Conclusion

While SQL Server 2022 offers the convenient GREATEST() function, older versions require alternative approaches. We explored creating a User-Defined Function, using the CASE statement, and employing the IIF function. Each method has its strengths and limitations, making the choice dependent on specific requirements and the SQL Server version in use.