stucampbell stucampbell - 4 years ago 108
SQL Question

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

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


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)
select ID, Col1, Col2, Col3
from TestTable
select cte.ID, Col1, Col2, Col3, TheMin from cte
ID, min(Amount) as TheMin
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 Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download