Finding the Maximum of Two Values in SQL Server

SQL Server doesn’t directly offer a MAX function that accepts multiple values like Math.Max in .NET. The built-in MAX function is an aggregate function designed to return the maximum value from a set of rows for a particular column. However, you can easily achieve the equivalent of a two-value maximum using several techniques. This tutorial explores those methods.

The Problem

Imagine you have a table named Order with columns NegotiatedPrice and SuggestedPrice, and you want to select the maximum of these two prices for each order. The following query won’t work as expected:

SELECT o.OrderId, MAX(o.NegotiatedPrice, o.SuggestedPrice)
FROM Order o

This is because MAX needs a column name to operate on, not two individual values.

Solution 1: The CASE Statement

The most straightforward approach is using a CASE statement. This allows you to define a condition to compare the two values and return the larger one.

SELECT o.OrderId,
       CASE
           WHEN o.NegotiatedPrice > o.SuggestedPrice THEN o.NegotiatedPrice
           ELSE o.SuggestedPrice
       END AS MaxPrice
FROM Order o;

This query compares NegotiatedPrice and SuggestedPrice for each row. If NegotiatedPrice is greater, it returns that value; otherwise, it returns SuggestedPrice. The AS MaxPrice clause assigns an alias to the resulting column, making it more readable. This method is compatible with all versions of SQL Server.

Solution 2: The IIF Function (SQL Server 2012 and later)

SQL Server 2012 introduced the IIF function, which provides a more concise way to write conditional expressions.

SELECT o.OrderId,
       IIF(o.NegotiatedPrice > o.SuggestedPrice, o.NegotiatedPrice, o.SuggestedPrice) AS MaxPrice
FROM Order o;

The IIF function takes three arguments: a boolean expression, a value to return if the expression is true, and a value to return if the expression is false. It is functionally equivalent to the CASE statement approach but often more readable for simple conditions.

Solution 3: Derived Tables with VALUES (SQL Server 2008 and later)

For more complex scenarios or when you need to find the maximum of several values, you can use a derived table (subquery) combined with the VALUES clause and the MAX aggregate function.

SELECT o.OrderId,
       (SELECT MAX(Price)
        FROM (VALUES (o.NegotiatedPrice), (o.SuggestedPrice)) AS AllPrices(Price)) AS MaxPrice
FROM Order o;

This query creates an inline table AllPrices containing the two price values. Then, it uses the MAX function to find the maximum value within this derived table. This method is especially useful when the number of values you need to compare is dynamic or comes from another query.

Solution 4: User-Defined Function (UDF)

You could create a User-Defined Function (UDF) to encapsulate the logic of finding the maximum of two values. While this allows for reusable code, it can sometimes impact performance.

CREATE FUNCTION dbo.InlineMax (@val1 INT, @val2 INT)
RETURNS INT
AS
BEGIN
  IF @val1 > @val2
    RETURN @val1;
  RETURN ISNULL(@val2, @val1); --Handle cases where @val2 is NULL
END;
GO

SELECT o.OrderId, dbo.InlineMax(o.NegotiatedPrice, o.SuggestedPrice) AS MaxPrice
FROM Order o;

Handling NULL Values

When dealing with NULL values, be mindful that comparisons involving NULL often result in UNKNOWN. To ensure correct results, you might need to use the ISNULL or COALESCE function to replace NULL values with a default value before making the comparison. For instance, in the UDF example above, the ISNULL(@val2, @val1) ensures that if @val2 is NULL, @val1 is returned instead.

Choosing the Right Approach

The best approach depends on your specific needs and the version of SQL Server you are using:

• For simple cases, the CASE statement or IIF function are usually the most efficient and readable.
• For more complex scenarios involving multiple values, derived tables with the MAX function offer more flexibility.
• UDFs can be useful for code reuse, but consider their potential performance impact.