How to Find the Maximum Value Across Multiple Columns in SQL Server?

Find the maximum value of multiple values ​​in SQL Server

In SQL Server, the MAX function is an aggregate function that operates on a single column and returns the maximum value among all rows for that column. However, sometimes we may need to find the maximum value in multiple columns.

For example, consider the following query:

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

This query attempts to find the maximum value of two columns NegotiatedPrice and SuggestedPrice. However, the MAX function does not support this operation.

To get the desired result we can use a subquery as shown in the code below:

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

This subquery creates a derived table named AllPrices that contains a single column named Price. The Price column contains the values ​​of NegotiatedPrice and SuggestedPrice. We then use the MAX function on this derived table to find the maximum value for each row.

This method is a convenient way to find the maximum value of multiple columns in SQL Server. It is also versatile and can be used to find other aggregated values ​​such as minimum, average, or sum.

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