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.
2 Answers
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
5 Comments
user3637879
Ahh! It worked.. but it gives me a single minimum value.. I actually have 1000 rows and 5 columns and I want to get a minimum value for each row.. Apologies for the confusion..Please help
user3637879
I basically want to add a new column to the data showing minimum of each row across the 5 columns
sgeddes
@user3637879 -- you need a row_number to group on. If you have an id, that would be best. If not, you can create one like this: sqlfiddle.com/#!3/cbdb4/4
user3637879
For example, Row 1: Col 1 is 0 Col 2 is 100 Col 3 is 200 Col 4 is 150 Col 5 is NULL, Row 2: Col 1 is 400 Col 2 is 120 Col 3 is 200 Col 4 is 150 Col 5 is NULL.. I want to add Col 6 as Market_Min with Row 1 value as 100 and Row 2 as 120
Richard Vivian
Great answer sgeddes. You can get the min val per row using the above if you have a row identifier to group by. I.e RecordId, MIN(ComineCol) .... stuff ... GROUP BY RecordId
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
100the correct answer? Are you looking for the minimum positive non-null number?