Reputation: 675
Let say, I have this Data: (Exam ID, Exam Name, & Exam Score)
Exam ID | Exam Name | Score
----------------------------
Exam_1 | Exam 1 | 95
Exam_2 | Exam 2 | 67
Exam_3 | Exam 3 | 77
Exam_4 | Exam 4 | 97
I want to compare the score and get only the highest score to show it using My SQL, Based on table above, I want to get only the highest score, it means Exam_4 | Exam 4| 97
I got an Idea to use sort descending
to do it. But I can't quite implement the logic correctly since I must Check Every ID and after that Compare the score, How Can I Solve this Problem Using MySQL?
Can someone Help me to solve this? I'm Quite Confused about this
EDIT 1
Sorry I forgot to mention this,
Every Exam can be take by Student at least 2 times, So the exact tables Will looks like this:
Student Id | Exam ID | Exam Name | Score
-------------------------------------
Student A | Exam_1 | Exam 1 | 95
Student A | Exam_2 | Exam 2 | 67
Student A | Exam_3 | Exam 3 | 77
Student A | Exam_4 | Exam 4 | 97 (Student A Highest Score)
Student B | Exam_1 | Exam 1 | 66
Student B | Exam_3 | Exam 3 | 87 (Student B Highest Score)
Student B | Exam_4 | Exam 4 | 57
Student C | Exam_1 | Exam 1 | 56 (Student C Highest Score)
Student C | Exam_2 | Exam 2 | 37
Student C | Exam_3 | Exam 3 | 47
Student D | Exam_1 | Exam 1 | 88 (Student D Highest Score)
Student D | Exam_4 | Exam 4 | 79
So what I want to Achieve is, I want to get The Highest score for Every Student such as Like This:
Student Id | Exam ID | Exam Name | Score
-------------------------------------
Student A | Exam_4 | Exam 4 | 97 (Student A Highest Score)
Student B | Exam_3 | Exam 3 | 87 (Student B Highest Score)
Student C | Exam_1 | Exam 1 | 56 (Student C Highest Score)
Student D | Exam_1 | Exam 1 | 88 (Student D Highest Score)
Upvotes: 0
Views: 83
Reputation: 42854
For 5+ MySQL version:
SELECT t1.*
FROM sourcetable t1
JOIN ( SELECT t2.`Student Id`, MAX(t2.Score) Score
FROM sourcetable t2
GROUP BY t2.`Student Id` ) t3 USING (`Student Id`, Score);
For MySQL 8+:
WITH cte AS ( SELECT *, RANK() OVER (PARTITION BY `Student Id` ORDER BY Score DESC) rnk
FROM sourcetable )
SELECT *
FROM cte
WHERE rnk = 1;
Anycase if some student have equal highest mark for 2 or more exams all of them will be returned.
Upvotes: 1