Getting Subject Position of a student from total scores using PHP

[Ninotech]

 Am working on a project that gets subject average, subject position and class position of a student in a class.

My challenge now is how to get Subject Position and class position of a student in a class. Please i need help.

This is my Table arrangement:

table name=academic_result

<?php
                      
// Subject Ranking
$stmt = $link->prepare("SELECT REG, term, subject, session, total, dense_rank() OVER( partition by subject ORDER BY total desc ) 
AS 'dense_rank' FROM academic_result WHERE class=? AND term=? AND session=?");
$stmt->bind_param("sss", $class, $term, $session);
$stmt->execute();
$checkSubject = $stmt->get_result();

$result_SUBPO = $checkSubject->fetch_assoc();
echo ordinal($result_SUBPO['dense_rank']);
?>

REG | class | session | term | subject | total
01  | js1   |200/2001 | 1T   | csc101  | 85
02  | js1   |200/2001 | 1T   | csc101  | 90
01  | js1   |200/2001 | 1T   | bus101  | 70
02  | js1   |200/2001 | 1T   | bus101  | 90
01  | js1   |200/2001 | 1T   | chem101 | 75
02  | js1   |200/2001 | 1T   | chem101 | 85

 

My query Output looks link this 

Subject | Total | Subject ranking | class position 
csc101  | 85    |1st              |
bus101  | 70    |1st              | 
chem101 | 75    |1st              |  

this what the query out put looks like, this is the result of a student with Reg No(01). Subject ranking(subject position) for a student is always 1st, this is where am having problem. The subject ranking (subject position) is alto vary depending on their subject total score.

I still want to get Class position of a student from the table.

Edited August 21, 2023 by Ninotech

[Barand]

IMHO dense_rank() gives an exaggerated picture of performance. If you get 90% and the other 19 students in your class score 100%, your dense_ranking is 2nd, which is pure BS. If 19 people did better than you then you came 20th (which is what rank() would give.

I have tried 2 or 3 times to install mysql version 8 with its window functions, but without success, so I had to do this the hard way. I sed two subqueries - one for the subject positions and the other for the class positions and joined them on REG.

SELECT subj.reg
     , subj.subject
     , subj.total
     , subj.subject_rank+0 as subject_rank
     , clas.class_rank+0 as class_rank
FROM 
    (
        SELECT 
               reg
             , @prevrank := CASE WHEN subject <> @prevsub
                                 THEN 1
                                 ELSE CASE WHEN total = @prevtot
                                           THEN @prevrank
                                           ELSE @prevrank + 1
                                      END
                            END as subject_rank
             , @prevsub := subject as subject
             , @prevtot := total as total
        FROM (
                SELECT reg
                     , subject
                     , total
                FROM academic_result
                WHERE class= 'js1' AND term='1T' AND session='200/2001'
                ORDER BY subject, total DESC
                LIMIT 9223372036854775808     -- MariaDB bug workaround
             ) subj_sorted
    ) subj
    JOIN 
    (
        SELECT reg
             , @prevcrank := CASE WHEN tot = @prevctot
                    THEN @prevcrank
                    ELSE @prevcrank + 1
                END as class_rank
             , @prevctot := tot as tot  
        FROM (
                SELECT reg
                     , sum(total) as tot
                FROM academic_result
                WHERE class= 'js1' AND term='1T' AND session='200/2001'
                GROUP BY reg
                ORDER BY tot DESC
                LIMIT 9223372036854775808
             ) cl_sorted 
     ) clas USING (reg)
     JOIN 
     ( SELECT @prevsub:=0, @prevtot:=0, @prevctot:=0, @prevrank := 0, @prevcrank := 0 ) init

ORDER BY class_rank";            

giving

+-----+---------+-------+--------------+------------+
| reg | subject | total | subject_rank | class_rank |
+-----+---------+-------+--------------+------------+
| 2   | bus101  | 90    | 1            | 1          |
| 2   | chem101 | 85    | 1            | 1          |
| 2   | csc101  | 90    | 1            | 1          |
| 1   | bus101  | 70    | 2            | 2          |
| 1   | chem101 | 75    | 2            | 2          |
| 1   | csc101  | 85    | 2            | 2          |
+-----+---------+-------+--------------+------------+