How to get Minimum minimum value of a row in sql

Question

I want to get the minimum value of a row in MS SQL like in below table I have minimum value is 4, it will print 4 in output column

Col1	Col2	Col3	Output
6	4	5	4

Answer 1

That was answered here: What's the best way to select the minimum value from several columns?

Using CROSS APPLY:

SELECT ID, Col1, Col2, Col3, MinValue
FROM YourTable
CROSS APPLY (SELECT MIN(d) AS MinValue FROM (VALUES (Col1), (Col2), (Col3)) AS a(d)) A

SQL Fiddle

Answer 2

You can use CROSS APPLY and MIN as follows:

SELECT MIN(x.cols) AS least_ --, MAX(x.cols) AS greatest_
FROM   your_table t
CROSS APPLY ( VALUES ( t.col1 ), ( t.col2 ), ( t.col3 ), ( t.col4 )) AS x ( cols );

Answer 3

Use CASE Expression to achieve this:

DECLARE @T TABLE( COL1 INT ,COL2 INT ,COL3 INT )
Insert into @T Values(6,4,5)

SELECT CASE WHEN COL1 < COL2 AND COL1 < COL3 THEN COL1 
            WHEN COL2 < COL1 AND COL2 < COL3 THEN COL2 
            WHEN COL3 < COL1 AND COL3 < COL2 THEN COL3 END 
FROM @T

Answer 4

This can be achieved using nested CASE expression also as follows:

select 
Col1,
Col2,
Col3,
Case 
    When (Col1 > Col2 AND Col1 > Col3 )
        Then Col1
    else 
        Case 
            When (Col2 > Col3)
                Then Col2
            else 
                Col3
        END
END as  Output
from
TableName