Get student that has the highest number of A's

Question

Take for example we have table Student_Marks that has columns Mark_ID (e.g1,2,3,4,5) and Mark_Name(E,D,C,B,A), and we have a table Student in relations with Student_Marks. How can we obtain the student that has the highest number of A's. Thanks!

Answer 1

Since you haven't provided the exact table structure and any sample data, this answer would be based on what I can comprehend from your question.

Let's say the Student table is as such :

Student_ID  Student_Name    Marks_ID
    1         John Doe          1
    1         John Doe          1
    1         John Doe          2
    1         John Doe          3
    1         John Doe          4
    1         John Doe          5
    1         Jane Doe          1
    1         Jane Doe          2
    1         Jane Doe          2
    1         Jane Doe          2
    1         Jane Doe          4

and the Student_Marks table is as such :

Marks_ID    Mark_Name
   1            A
   2            B
   3            C
   4            D
   5            E

Then you can use the following query :

SELECT      TOP 1 students.Student_Name, 
            COUNT(marks.Mark_Name) AS TotalOfGradeA
FROM        Student students
INNER JOIN  Student_Marks marks ON students.Marks_ID = marks.Marks_ID
WHERE       marks.Mark_Name = 'A'
GROUP BY    students.Student_Name
ORDER BY    COUNT(marks.Mark_Name) DESC

NOTE : You haven't specified how you would like to handle 'ties' so I have written the query to handle it in any particular fashion as well.

You can see this here -> http://rextester.com/SFVLA27100

Hope this helps!!!