SQL Puzzle | Find Minimum from (col1,col2,col3,col4) with ColumnName | SQL Interview Question

In this puzzle you have to find minimum from multiple columns named col1,col2,col3 and col4 with the name of the column from which you got the minimum value. Please check the sample input and the expected output.

Sample Input

Id Col1 Col2 Col3 Col4
1 10 29 40 74
2 14 19 41 11
3 9 19 47 77
4 27 79 43 70
5 80 99 46 78
6 15 13 43 13
7 16 18 48 70

Expected Output

Id Col1 Col2 Col3 Col4 MinVal ColumnName
1 10 29 40 74 10 col1
2 14 19 41 11 11 col4
3 9 19 47 77 9 col1
4 27 79 43 70 27 col1
5 80 99 46 78 46 col3
6 15 13 43 13 13 col2
7 16 18 48 70 16 col1

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

 -- CREATE TABLE FindMinWithItsColumn ( Id INT ,Col1 INT ,Col2 INT ,Col3 INT ,Col4 INT ) GO INSERT INTO FindMinWithItsColumn VALUES (1,10,29,40,74), (2,14,19,41,11), (3,9,19 ,47,77), (4,27,79,43,70), (5,80,99,46,78), (6,15,13,43,13), (7,16,18,48,70) GO SELECT * FROM FindMinWithItsColumn GO --

Rules/Restrictions

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

SOLUTION – 1

 -- SELECT * , CASE WHEN MinVal = col1 THEN 'col1' WHEN MinVal = col2 THEN 'col2' WHEN MinVal = col3 THEN 'col3' WHEN MinVal = col4 THEN 'col4' END ColumnName FROM FindMinWithItsColumn CROSS APPLY ( SELECT MIN(v) MinVal FROM ( VALUES (col1) , (col2), (col3) ,(col4) ) as value(v) )u --

Output-1

 -- Id Col1 Col2 Col3 Col4 MinVal ColumnName ----------- ----------- ----------- ----------- ----------- ----------- ---------- 1 10 29 40 74 10 col1 2 14 19 41 11 11 col4 3 9 19 47 77 9 col1 4 27 79 43 70 27 col1 5 80 99 46 78 46 col3 6 15 13 43 13 13 col2 7 16 18 48 70 16 col1 (7 rows affected) --

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

Pawan Khowal

Pawan is a SQL Server Developer. 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 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/