How to fetch the correct students with the highest scores

Issue

I’m trying to get the highest score in a particular subject and then display the name of the student that has that highest score.

I have the query which gets the highest scores in each subjects in a class but it’s not getting the correct student_id.

This is my query:

$subjectMaxQry = $this->db->query(
        "SELECT `student_id`, max(".$totField.") AS highest, 
                (SELECT firstname FROM students WHERE students.id = student_id) AS firstname, 
                (SELECT lastname FROM students WHERE students.id = student_id) AS lastname 
        FROM ".$table." 
        WHERE `session_id` = '".$sch_setting->session_id."' 
        AND `class_id` = '".$class_id."' 
        AND `subject_id` = '".$subName['sub_id']."'");

$highestMark = $subjectMaxQry->row_array();

The query shows the highest score of each subjects in a class but the issue is that the student’s name being displayed is wrong. It does not show the student’s name with the highest score in a subject.

Solution

First of all, please read up on prepared statements in PHP. At the moment, your code is most likely prone to SQL injections. Using prepared statements mitigates this security risk.

As for your query itself, you could use something like this:

SELECT a.student_id, students.firstname, students.lastname, a.score
FROM scores a
INNER JOIN (
    SELECT class_id, subject_id, MAX(score) score
    FROM scores
    GROUP BY class_id, subject_id
) b 
ON a.score = b.score 
AND a.class_id = b.class_id 
AND a.subject_id = b.subject_id
INNER JOIN students ON students.id=a.student_id
WHERE a.class_id = 1
AND a.subject_id = 1

This query performs an inner join on itself to find out the maximum score per class_id and subject_id and joins on those fields to get the actual data in the table with the highest score. Then, another join is included to get the data from the students table.

You can use the a.class_id=1 and a.subject_id=1 lines to filter and the desired classes and subjects.

You can check this DB fiddle to play around with it.

Answered By – vixducis

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published