CODEWITHSUNDEEP
sqlsql-serverrowmin
Muhammad Irtiza
Muhammad Irtiza

Reputation: 39

How to get Minimum minimum value of a row in sql

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

Upvotes: 0

Views: 1062

Answers (4)

Teja Goud Kandula
Teja Goud Kandula

Reputation: 1574

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

Upvotes: 0

Naivre
Naivre

Reputation: 97

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

Upvotes: 5

Thiyagu
Thiyagu

Reputation: 1330

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

Upvotes: 0

Popeye
Popeye

Reputation: 35900

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

Upvotes: 2

Related Questions