Tags
Complex SQL Challenges, Complex TSQL Challenge, Interview Qs.SQL SERVER Questions, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Learn complex SQL, Learn SQL, Learn T-SQL, PL/SQL Challenges, Puzzles, Queries for SQL Interview, SQL, SQL 2012, SQL Challenge, SQL Challenges, SQL pl/sql puzzles, SQL Puzzles, SQL SERVER Interview questions, SQL SERVER2005/2008, SQL Sudoku, SQLSERVER, T SQL Puzzles, T-SQL Challenge, Tough SQL Challenges, Tough SQL Puzzles, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Queries
T-SQL Query | [Finding Maximum Value from multiple columns] – Write a query which will find maximum value from multiple columns of the table
Sample Input
| Year1 | Max1 | Max2 | Max3 |
| 2001 | 10 | 101 | 87 |
| 2002 | 103 | 19 | 88 |
| 2003 | 21 | 23 | 89 |
| 2004 | 27 | 28 | 91 |
Expected Output
| Year1 | MaxValue |
| 2001 | 101 |
| 2002 | 103 |
| 2003 | 89 |
| 2004 | 91 |
Rules/Restrictions
- The solution should be should use “SELECT” statement or “CTE”.
- Send your solution to pawankkmr@gmail.com
- Do not post you solution in comments section
Script
Use the below script to generate the source table and fill them up with the sample data.
--Create table CREATE TABLE TestMax ( Year1 INT ,Max1 INT ,Max2 INT ,Max3 INT ) GO --Insert data INSERT INTO TestMax VALUES (2001,10,101,87) ,(2002,103,19,88) ,(2003,21,23,89) ,(2004,27,28,91) --Select data Select Year1,Max1,Max2,Max3 FROM TestMax |
Update May 14 | Solution
--
---------------------------------------
--Sol 1 | Pawan Kumar Khowal
---------------------------------------
SELECT Year1,
(
SELECT Max(v)
FROM (
VALUES (max1) , (max2), (max3)
) as value(v)
) as mmax
FROM TestMax
---------------------------------------
--Sol 2 | Pawan Kumar Khowal
---------------------------------------
SELECT Year1, MAX(maxy) maxyy
FROM
(
SELECT Year1 , Max1 maxy FROM TestMax
UNION ALL
SELECT Year1 , Max2 FROM TestMax
UNION ALl
SELECT Year1 , Max3 FROM TestMax
) a GROUP BY YEAR1
---------------------------------------
--Sol 3 | Pawan Kumar Khowal
---------------------------------------
SELECT Year1,
CASE WHEN Max1 > Max2 AND MAX1 > Max3 THEN MAX1
WHEN Max2 > Max1 AND MAX2 > Max3 THEN MAX2
WHEN Max3 > Max2 AND MAX3 > Max1 THEN MAX3
END AS Maxx
FROM TestMax
--
|
Add a comment if you have any other solution in mind. We all need to learn.
Keep Learning
Improving my SQL BI Skills 
select year1,
row_max = max(abc)
from testmax
unpivot
(
abc
for mol in (max1, max2, max3)
) piv
group by year1
LikeLiked by 1 person
Good One !!
LikeLike
Pingback: SQL Puzzle | Find Minimum Value from Multiple Columns Puzzle | Improving my SQL BI Skills
Select
tm.Year1,
crossapply.maxed as MaxValue
FROM TestMax tm
cross apply
(
select MAX(currentone) AS maxed from
(
select tm.Max1 as currentone
union all
select tm.Max2 as currentone
union all
select tm.Max3 as currentone
) as grouped
) as crossapply
LikeLiked by 1 person
SELECT
Year1,
Max1,
Max2,
Max3,
GREATEST(Max1, Max2, Max3) AS MaxValue
FROM
TestMax; is the best way, as I think
LikeLike