What's the best way to select the minimum value from several columns?

When LEAST just isn't an option– Use CASE or CROSS APPLY

In a SQL Server environment where LEAST isn’t available, we have a few alternative methods we can use to achieve the same result.

Swing the CASE

"Reality is often disappointing." —Thanos, and also SQL Server developers when they realize LEAST is missing. But wait, there's still hope with CASE:

SELECT 
  CASE
    WHEN col1 <= col2 AND col1 <= col3 THEN col1
    WHEN col2 <= col1 AND col2 <= col3 THEN col2
    ELSE col3
  END AS min_col
FROM table;


This case allows SQL to act as a fair judge, stating clearly: "I shall select the smallest value among the given columns, and I DON'T CARE if there's a tie."

Pivoting with CROSS APPLY and VALUES

"Clever as the devil and twice as pretty." SQL Server may not have LEAST, but it's got tricks up its sleeve. Welcome CROSS APPLY with VALUES:

SELECT p.ProductID, p.ProductName, MinPrice.Price AS MinPrice
FROM Products AS p
CROSS APPLY (
    SELECT MIN(Price) AS Price
    FROM (VALUES (p.Col1), (p.Col2), (p.Col3)) AS Value(Price)
) AS MinPrice;


Here, CROSS APPLY pulls a fast one by unwrapping an array-like column, lining up the values, and then picking the minimum, all without using the actual UNPIVOT statement.

Restructuring for efficiency

In the perfect world of Normal Forms, you wouldn't find array-like columns—they'd get their own rows. This reshaping gives a performance boost when performing aggregate functions (like MIN) by eliminating unnecessary complexity. Now that's what I call killing two birds with one SELECT.

UNION ALL the things and find the MIN

When in doubt, UNION ALL it out. Create one big UNION of columns and seek the minimum in this crowd:

SELECT MIN(value) AS min_val
FROM (
    SELECT col1 AS value FROM table  /* The first nominee */
    UNION ALL
    SELECT col2 FROM table           /* The second step on our way to victory */
    UNION ALL
    SELECT col3 FROM table           /* And the last, but not least */
) AS combined;                      /* Now, let's see who the winner is! */


This method rounds up all the column values into one single column, and afterwards finds the minimum value.

SQL Server 2022 to the rescue!

SQL Server finally learned the short and sweet LEAST function in 2022. Patience is a virtue, folks:

SELECT LEAST(col1, col2, col3) AS min_col FROM table;


With this function, finding the smallest value among various columns is much simpler and your SQL statements are way more readable.

Pivot smart, not hard

If your database schema doesn't let you normalize, pivot your data—transform the separate columns into pseudo-columns, then apply the MIN function. This handy move is your best bet when the columns are dynamic.

UNPIVOT for performance...just kidding

Make no mistake, heavy-duty tasks like UNION or UNPIVOT over large datasets can eat up your resources and corrupt performance. Always test and refactor as necessary. Long story short, keep an eye on your server—it could be burning.