SQL Puzzle | Find Minimum Value from Multiple Columns Puzzle

In this puzzle you have to find minimum value from multiple columns other than the NULL and zero value. For more details please check the sample input and expected output.

Sample Input

Id a b c d e f
1 NULL 0 918.09 162.12 NULL 675.72
2 NULL 0 761.09 19.12 NULL 231.72

Expected Output

Id MinimumValue
1 162.12
2 19.12

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE FindMinimumNotNULL
(
	 Id INT
	,a INT
	,b INT 
	,c DECIMAL(10,2)
	,d DECIMAL(10,2)
	,e INT
	,f DECIMAL(10,2)
)
GO

INSERT INTO FindMinimumNotNULL (Id,a,b,c,d,e,f) Values 
(1,NULL,0,918.09,162.12,NULL,675.72),
(2,NULL,0,761.09,19.12,NULL,231.72)
GO

SELECT * FROM FindMinimumNotNULL
GO

--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution – 1 | USING VALUES

--


SELECT Id,
(
       SELECT MIN(v)
       FROM 
	   (
          VALUES (a) , (b), (c) ,(d) ,(e) , (f) 
       ) as value(v)
	   WHERE v > 0
) as MinimumValue 
FROM FindMinimumNotNULL 

--

Output-1

--                            

Id          MinimumValue
----------- ---------------------------------------
1           162.12
2           19.12

(2 rows affected)

--

Solution – 2 | USING UNPIVOT METHOD

--


SELECT Id,MIN(mmin) MinimumValue
FROM 
(
    SELECT Id,CAST(a AS DECIMAL(10,2)) a 
    ,CAST(b AS DECIMAL(10,2)) b 
	,CAST(c AS DECIMAL(10,2)) c
	,CAST(d AS DECIMAL(10,2)) d 
	,CAST(e AS DECIMAL(10,2)) e
	,CAST(f AS DECIMAL(10,2)) f
    FROM FindMinimumNotNULL
) t
UNPIVOT (mmin FOR vals IN (a,b,c,d,e,f)) u
WHERE mmin > 0
GROUP BY Id

--

Output-2

--                            

Id          MinimumValue
----------- ---------------------------------------
1           162.12
2           19.12

(2 rows affected)

--

Related Puzzle

SQL Puzzle URL
Finding Maximum Value from multiple columns Puzzle https://msbiskills.com/2015/03/19/310/

Add a comment if you have any other or better solution in mind. I would love to learn it. We all need to learn.

Author Introduction: Pawan Khowal

Pawan is a SQL Server Expert. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail URL
☛ SQL Advance Puzzles https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com

Advertisements