Php and Mysql to rank student based on their total scores and give . Also not rank consecutively if their is ties

Question

I have a very simple MySQL table where I save subjects with Exam and CA Scores for each students with their admin_no. It looks like that:

 admin_no   subject     ca  exam    year       class_s 
10/00182     IRS        39  56    2014/2015     Grade 2
10/00177    English     39  59    2014/2015     Grade 2
10/00177    Mathematics 34  59    2014/2015     Grade 2
10/00177    Basic       37  59    2014/2015     Grade 2
10/00177    Social      39  60    2014/2015     Grade 2
10/00177    Yoruba      33  59    2014/2015     Grade 2
09/00159    English     37  59    2014/2015     Grade 2
09/00159    Mathematics 35  60    2014/2015     Grade 2
09/00159    Basic       39  59    2014/2015     Grade 2
.......................................................

I used the mysql query bellow to sum but exam scores and CA of all the subjects and then total all the scores of each students as total_scores and then rank the total scores:

SELECT admin_no,rank,total_score
FROM (SELECT *,  IF(@marks=(@marks:=total_score), @auto, @auto:=@auto+1) AS rank 
FROM (SELECT * FROM 
  (SELECT admin_no, SUM(exam)+SUM(ca) AS total_score,year,class_s
    FROM subjects_1 ,
    (SELECT @auto:=0, @marks:=0) as init WHERE `class_s`='Grade 2' and `year`='2014/2015'
     GROUP BY admin_no ) sub ORDER BY total_score DESC)t) as result 

The Output of the query:

 admin_no   rank total_score    
08/00076    1   1615
10/00170    2   1613
12/00300    3   1609
09/00091    4   1604
10/00182    5   1600
09/00159    6   1583
10/00177    7   1574
09/00152    8   1561
09/00165    9   1540
10/00176    10  1516
13/00354    11  1497
10/00178    12  1470
14/00348    13  1409
**14/00346  14  12
15/00371    14  12
09/00156    15  7**

Problems:The out put is good but having problem with tallies in the last three ranks .i.e the total scores 12 appear twice for 14/00346 and 15/00371 and they were given the same rank which is good but next to 12 is 7 and it is ranked 15 instead of 17. Pls help me I dont want the Rank to be consecutive if their is ties in the total_scores .

Bellow is a copy of my mysql data http://youth-arena.com/portal/sql.sql

Here are the php query codes

http://youth-arena.com/portal/query.txt

Answer 1

Try this.

I've not included year and class_s in there as you may not get a reliable rowset (as you're GROUPing by admin_no).

I've included both ordinal and competition ranks - pick whichever one you need.

More info here: https://en.wikipedia.org/wiki/Ranking

I don't know the guidelines of who gets the higher rank when tied, but as a suggestion - you could do the number of subject a student studies to determine this (included in SQL for example purposes).

SET @prev_value = NULL;
SET @rank_count = 0;
SET @rank_increasing = 0;
SELECT @rank_increasing := @rank_increasing + 1 AS ordinal_rank
     , CASE
       WHEN @prev_value = a.total_score
          THEN @rank_count
       WHEN @prev_value := a.total_score
          THEN @rank_count := @rank_increasing 
        END AS competition_rank
     , a.*
  FROM ( SELECT admin_no
              , SUM(exam) + SUM(ca) AS total_score
              , SUM(exam) AS sum_exam
              , SUM(ca) AS sum_ca
              , COUNT(DISTINCT subject) AS subject_count
           FROM subjects_1
          GROUP BY admin_no
          ORDER BY total_score DESC
       ) a

Screenshot snippet