I'm in a situation where I want to get the minimum value from of 6 columns.
I've found three ways so far to accomplish this, but I have concerns with the performance of these methods and would like to know which would be better for performance.
The first method is to use a big case statement. Here's an example with 3 columns, based on the example in the link above. My case statement would be much longer since I will be looking at 6 columns.
Select Id,
Case When Col1 <= Col2 And Col1 <= Col3 Then Col1
When Col2 <= Col3 Then Col2
Else Col3
End As TheMin
From MyTable
The second option is to use the UNION
operator with multiple select statements. I would put this in an UDF that accepts an Id parameter.
select Id, dbo.GetMinimumFromMyTable(Id)
from MyTable
and
select min(col)
from
(
select col1 [col] from MyTable where Id = @id
union all
select col2 from MyTable where Id = @id
union all
select col3 from MyTable where Id = @id
) as t
And the 3rd option I found was to use the UNPIVOT operator, which I didn't even know existed until just now
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
Because of the table size, and frequency in which this table is queried and updated, I am concerned about the performance impact these queries would have on the database.
This query will actually be used in a join to a table with a few million records, however the records returned will be reduced to around a hundred records at a time. It will get run many times throughout the day, and the 6 columns I am querying are frequently updated (they contain daily stats). I do not think there are any indexes on the 6 columns I am querying.
Which of these methods is better for performance when trying to get the minimum of multiple columns? Or is there another better method that I don't know of?
I am using SQL Server 2005
Sample Data & Results
If my data contained records like this:
Id Col1 Col2 Col3 Col4 Col5 Col6 1 3 4 0 2 1 5 2 2 6 10 5 7 9 3 1 1 2 3 4 5 4 9 5 4 6 8 9
The end result should be
Id Value 1 0 2 2 3 1 4 4