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
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