CODEWITHSUNDEEP
sqlminimum
user3637879
user3637879

Reputation: 1

Minimum values between multiple columns ignoring zero and null value columns

I want to create a new variable called Market_Min from the following 5 columns where:

Col 1 is 0
Col 2 is 100
Col 3 is 200
Col 4 is 150
Col 5 is NULL

The answer should be 100 in this case.

Upvotes: 0

Views: 1326

Answers (2)

Richard Vivian
Richard Vivian

Reputation: 1750

To use a different approach using fn_Split

DECLARE @Temp AS TABLE (col1 int, col2 int,  col3 int, col4 int , col5 int)

INSERT INTO @Temp
    (col1 , col2 ,  col3 , col4 , col5 )
VALUES
    ( 0, 200, 100, 150,NULL)
SELECT
    *
    ,
    (
    SELECT 
        MIN(Value) 
    FROM 
        dbo.fn_Split
        (
          CAST(ISNULL(NULLIF(col1,0),99999) AS VARCHAR(10)) +','
        + CAST(ISNULL(NULLIF(col2,0),99999) AS VARCHAR(10)) +','
        + CAST(ISNULL(NULLIF(col3,0),99999) AS VARCHAR(10)) +','
        + CAST(ISNULL(NULLIF(col4,0),99999) AS VARCHAR(10)) +','
        + CAST(ISNULL(NULLIF(col5,0),99999) AS VARCHAR(10))
        ,','
        )
    ) AS MinNonZeroNonNullValue
FROM
    @Temp

You will need to add the fn_Split function to the server. Which you can download Here

Upvotes: 0

sgeddes
sgeddes

Reputation: 62831

Here is one option using union all to get the minimum value across the entire table:

select min(combinedcol)
from (
    select col1 combinedcol from yourtable union all
    select col2 from yourtable union all
    select col3 from yourtable union all
    select col4 from yourtable union all
    select col5 from yourtable
) t
where coalesce(combinedcol,0) > 0

Edit base on comments

If you need a minimum value per row, you can introduce a row_number in your subquery and group by it:

select rn, min(combinedcol)
from (
    select row_number() over (order by (select null)) rn, col1 combinedcol from yourtable union all
    select row_number() over (order by (select null)) rn, col2 from yourtable union all
    select row_number() over (order by (select null)) rn, col3 from yourtable union all
    select row_number() over (order by (select null)) rn, col4 from yourtable union all
    select row_number() over (order by (select null)) rn, col5 from yourtable
) t
where coalesce(combinedcol,0) > 0
group by rn

Upvotes: 1

Related Questions