CODEWITHSUNDEEP
mysql
Rakish Frisky
Rakish Frisky

Reputation: 675

How to Get a Highest Number Score after Compare it in MySQL

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

Answers (1)

Akina
Akina

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

Related Questions