How to fetch the correct students with the highest scores

Question

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.

Please add sample records, and what the expected return should be. — user3783243, Aug 30 '22 at 17:55
Please read [Tips for asking a good Structured Query Language (SQL) question](https://meta.stackoverflow.com/questions/271055/tips-for-asking-a-good-structured-query-language-sql-question) and update the question with data examples, expected result, and what MySQL version are you using — Ergest Basha, Aug 30 '22 at 17:56

vixducis · Accepted Answer · 2022-08-31T12:01:30.210

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.

This works fine but I just noticed that it doesn't fetch all the records. For some reasons, some subjects are being omitted. I'm only using ```a.class_id=1``` to filter, so It should fetch all subjects — Esepebbles, Sep 02 '22 at 15:11
This seems to be working correctly for me. I updated the DB fiddle with the new filter (and added `subject_id` to the output): https://www.db-fiddle.com/f/aaGrqs4bXomWdotqm7okXe/1 This outputs the records for both `subject_id` 1 and 2. — vixducis, Sep 04 '22 at 15:15

How to fetch the correct students with the highest scores

1 Answers1