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

SQL Server: Efficiently Finding the Minimum Value Across Multiple Columns

Finding the minimum value across multiple columns in a SQL Server table is a common data analysis task. This article presents efficient methods for achieving this, focusing on SQL Server 2005 and beyond.

Let's assume a table with columns 'Col1', 'Col2', and 'Col3'. Our goal is to create a new column, 'TheMin', containing the minimum value from these three columns for each row.

Method 1: Using CASE Expressions

A straightforward approach uses a CASE expression to compare the column values and assign the minimum. This is efficient for a small number of columns:

<code class="language-sql">SELECT Id,
       CASE 
           WHEN Col1 < Col2 AND Col1 < Col3 THEN Col1
           WHEN Col2 < Col1 AND Col2 < Col3 THEN Col2
           ELSE Col3
       END AS TheMin
FROM YourTableNameHere;</code>

This CASE statement compares the values and selects the smallest.

Method 2: Using UNPIVOT for Multiple Columns

When dealing with many columns, the UNPIVOT operator offers a more scalable solution. UNPIVOT transforms multiple columns into a single column, allowing for easy aggregation:

<code class="language-sql">WITH cte (ID, Col1, Col2, Col3) AS (
    SELECT ID, Col1, Col2, Col3
    FROM TestTable
)
SELECT cte.ID, Col1, Col2, Col3, TheMin
FROM cte
JOIN (
    SELECT
        ID, MIN(Amount) AS TheMin
    FROM cte
    UNPIVOT (Amount FOR AmountCol IN (Col1, Col2, Col3)) AS unpvt
    GROUP BY ID
) AS minValues ON cte.ID = minValues.ID;</code>

UNPIVOT reshapes the data, then MIN() finds the minimum within each ID group. The result is joined back to the original table. This method is more maintainable and efficient with a larger number of columns than nested CASE statements.

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