asked
Max value from multiple columns
I have a table name student with following structure : Create table dev.Student (SID int, Sname varchar(255), Hindi int, Eng int, Math int) Insert into dev.Student values (1,'Abhi',40,50,60) Insert into dev.Student values(2,'Vishal',50,60,70) Insert into dev.Student values (3,'Dipta',60,70,80) Insert into dev.Student values (4,'Amit',70,80,NULL) I need output query for 1. Max marks of a student along with name, SID & subject. Thanks in advance!
Also, do you only have those three columns for scores, or are you just showing three as a sample?
And what do you want in the event of a tie? ie someone scores 70 in both English & Hindi, but only 60 in Math[s]?
Thanks. and the second question - the one about the columns? And what about the first - what have you tried so far?
Try this sample SELECT CASE WHEN Date1 >= Date2 AND Date1 >= Date3 THEN Date1 WHEN Date2 >= Date1 AND Date2 >= Date3 THEN Date2 WHEN Date3 >= Date1 AND Date3 >= Date2 THEN Date3 ELSE Date1 END AS MostRecentDate
I see what you've done there and that should work in this situation. My advice to the OP would be create a set of normalised tables. In the current format the data is hard to query and what happens when they add a new subject in lets 'Science' does that mean that they have to then add a new column in and then change all their CASE statements to at another column? Those CASE Statements will get long or they could UNPIVOT them. So to surmise, the OP should put the subjects into Subject and the Students into the student tables and then join them.
I agree with @WRBI that this would be much easily solved if the data was restructured into normalised tables, however out of interest I thought I'd post this solution. It's limited by the hardcoding of the subjects (but that's because of the non-normalized structure) and it handles ties (as Kev got 80 in both Eng and Maths). It uses a simple cross apply across all the scores to work out the top score, and then query back into a union of all the scores split by subject, to return the subject(s) that match that score. if object_id('tempdb..#Student') is not null drop table #Student; create table #Student (SID int, Sname varchar(255), Hindi int, Eng int, Math int) Insert into #Student values (1,'Abhi',40,50,60) Insert into #Student values(2,'Vishal',50,60,70) Insert into #Student values (3,'Dipta',60,70,80) Insert into #Student values (4,'Amit',70,80,NULL) Insert into #Student values (5,'Kev',70,80,80) select s1.SID, s1.Sname, MaxVal.TopScore, subjects.Subject, subjects.Score FROM #Student s1 cross apply ( SELECT MAX(val) TopScore FROM (VALUES (Hindi) , (Eng) , (Math) ) AS value(val) ) AS MaxVal left join ( select SID, 'Hindi' as Subject, Hindi as Score from #Student union all select SID, 'Eng' as Subject, Eng as Score from #Student union all select SID, 'Math'as Subject, Math as Score from #Student ) subjects on subjects.SID = s1.SID and Score = MaxVal.TopScore SID Sname TopScore Subject Score ----------- ----------- ----------- ------- ----------- 1 Abhi 60 Math 60 2 Vishal 70 Math 70 3 Dipta 80 Math 80 4 Amit 80 Eng 80 5 Kev 80 Eng 80 5 Kev 80 Math 80 (6 rows affected)
19 People are following this question.
