For simplicity think that i have a Query which return a row based on student_id, and it contain marks of six quiz with column names like Quiz1 Quiz2 Quiz3 Quiz4 Quiz5 Quiz6.
I want to select only the top 3 highest marks secure by the student and then take its average and then compare that value of average with another column "Pass_Avg" if the value of top 3 quiz mark average is greater than equal to the value of "Pass_Avg" then just simply change the value of result column with 'P' else 'F'.
So i was trying to get the highest value by doing something like this
SELECT CASE WHEN Quiz1 >= Quiz2 AND Quiz1 >= Quiz3 and Quiz1 >= Quiz4 and Quiz1 >= Quiz5 and Quiz1 >= Quiz6 THEN Quiz1 WHEN Quiz2 >= Quiz1 AND Quiz2 >= Quiz3 and Quiz2 >= Quiz4 and Quiz2 >= Quiz5 and Quiz2 >= Quiz6 THEN Quiz2 WHEN Quiz3 >= Quiz1 AND Quiz3 >= Quiz2 and Quiz3 >= Quiz4 and Quiz3 >= Quiz5 and Quiz3 >= Quiz6 THEN Quiz3 WHEN Quiz4 >= Quiz1 AND Quiz4 >= Quiz2 and Quiz4 >= Quiz3 and Quiz4 >= Quiz5 and Quiz4 >= Quiz6 THEN Quiz4 WHEN Quiz5 >= Quiz1 AND Quiz5 >= Quiz2 and Quiz5 >= Quiz3 and Quiz5 >= Quiz4 and Quiz5 >= Quiz6 THEN Quiz5 WHEN Quiz6 >= Quiz1 AND Quiz6 >= Quiz2 and Quiz6 >= Quiz3 and Quiz6 >= Quiz4 and Quiz6 >= Quiz5 THEN Quiz6 ELSE Quiz1 END AS Highest from dbo.Student where student_id= '01'
Can any one please suggest that how should i try to do this since i am having difficulty in getting the second and third highest value.....