How to Find the Maximum of Two Columns in SQL Server?

Determining the maximum value of two columns in SQL Server: Comparison with .NET’s Max function

In .NET, the Max function provides a convenient way to determine the larger of two numbers. However, the MAX function behaves differently when using SQL Server. It is commonly used as an aggregate function that returns the maximum value of a specified column across all rows in a table.

For instances where the Max function needs to operate on two specific values ​​rather than an entire column, SQL Server provides some options. A common approach is to utilize a UNION statement, where you create a new table containing two columns of values ​​to be compared, and then apply the MAX function to the combined table.

However, this approach can get complicated when dealing with multiple columns, and it has limitations in handling null values. A more elegant solution was introduced in SQL Server 2008, which involves using derived tables.

The provided answer suggests a powerful and flexible solution that utilizes derived tables:

<code class="language-sql">SELECT o.OrderId,
       (SELECT MAX(Price)
        FROM (VALUES (o.NegotiatedPrice),(o.SuggestedPrice)) AS AllPrices(Price)) AS MaxPrice
FROM Order o</code>

This derived table approach offers a number of advantages:

• Simplify code by eliminating the need for UNION, PIVOT, UNPIVOT and CASE statements.
• Handle null values ​​efficiently.
• Supports using different aggregate functions such as MIN, AVG or SUM.
• Allows customization of table and column names to improve readability and understanding.
• Facilitates determining multiple aggregates using derived tables in SQL Server 2008 and later.

The above is the detailed content of How to Find the Maximum of Two Columns in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!