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.
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
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
You can use the
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