This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Check the below input data and require output data to find max value from multiple columns.
Input Data:
|
1 2 3 4 5 6 |
Class Mark1 Mark2 Mark3 ---------- ----------- ----------- ----------- A 88 98 52 B 85 54 93 C 65 87 45 D 35 77 63 |
Require output data:
|
1 2 3 4 5 6 |
Class MaxMarks ---------- ----------- A 98 B 93 C 87 D 77 |
Create a table with sample data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
CREATE TABLE MaxClassMarks ( Class VARCHAR(10) ,Mark1 INT ,Mark2 INT ,Mark3 INT ) GO INSERT INTO MaxClassMarks VALUES ('A',88,98,52) ,('B',85,54,93) ,('C',65,87,45) ,('D',35,77,63) GO |
Solution 1: Use CASE .. WHEN
|
1 2 3 4 5 6 |
SELECT Class, CASE WHEN mark1 > mark2 AND mark1 > mark3 THEN mark1 WHEN mark2 > mark1 AND mark2 > mark3 THEN mark2 WHEN mark3 > mark2 AND mark3 > mark1 THEN mark3 END AS MaxMarks FROM MaxClassMarks |
Solution 2: Use Subquery and MAX()
|
1 2 3 4 5 6 7 8 |
SELECT Class, ( SELECT Max(T) FROM ( VALUES (mark1) , (mark2), (mark3) ) as value(T) ) as maxmark FROM MaxClassMarks |
Solution 3: Use MAX() with Group BY
|
1 2 3 4 5 6 7 8 9 |
SELECT Class, MAX(maxmark) maxmark FROM ( SELECT Class , mark1 as maxmark FROM MaxClassMarks UNION ALL SELECT Class , mark2 as maxmark FROM MaxClassMarks UNION ALl SELECT Class , mark3 as maxmark FROM MaxClassMarks ) a GROUP BY Class |
Please try the different solution for this puzzle and share it via comment...
