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

Question

Given the following table in SQL Server 2005:

ID   Col1   Col2   Col3 --   ----   ----   ---- 1       3     34     76   2      32    976     24 3       7    235      3 4     245      1    792 

What is the best way to write the query that yields the following result (i.e. one that yields the final column - a column containing the minium values out of Col1, Col2, and Col 3 for each row)?

ID   Col1   Col2   Col3  TheMin --   ----   ----   ----  ------ 1       3     34     76       3 2      32    976     24      24 3       7    235      3       3 4     245      1    792       1 

UPDATE:

For clarification (as I have said in the coments) in the real scenario the database is properly normalized. These "array" columns are not in an actual table but are in a result set that is required in a report. And the new requirement is that the report also needs this MinValue column. I can't change the underlying result set and therefore I was looking to T-SQL for a handy "get out of jail card".

I tried the CASE approach mentioned below and it works, although it is a bit cumbersome. It is also more complicated than stated in the answers because you need to cater for the fact that there are two min values in the same row.

Anyway, I thought I'd post my current solution which, given my constraints, works pretty well. It uses the UNPIVOT operator:

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 

I'll say upfront that I don't expect this to offer the best performance, but given the circumstances (I can't redesign all the queries just for the new MinValue column requirement), it is a pretty elegant "get out of jail card".

Answer 1

There are likely to be many ways to accomplish this. My suggestion is to use Case/When to do it. With 3 columns, it's not too bad.

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