CODEWITHSUNDEEP
phppdo
Hamzat Luqman
Hamzat Luqman

Reputation: 69

Php to Rank Scores consecutive if their is ties in the total_scores

This is the code I used:

SELECT admin_no,rank,total_score
FROM (SELECT *,  @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

This is the Output::

 admin_no   rank    total_score Descending 1    
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    15  12
09/00156    16  7

The Problem here is that both student 14/00346 and 15/00371 scored 12 marks and they were ranked 14th and 15th respectively while 09/00156 with 7 marks ranked 16. What I want is .......................

Admin       Rank Scores
14/00348    13  1409
14/00346    14  12
15/00371    14  12
09/00156    16  7

Please help me out

Upvotes: 0

Views: 103

Answers (1)

vinz
vinz

Reputation: 566

SET @total_score = 0;

SELECT admin_no, CASE WHEN @total_score = total_score THEN rank - 1 ELSE rank END rank, @total_score := total_score total_score FROM 
(
    SELECT admin_no,rank,total_score
    FROM (SELECT *,  @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) t2
) result

Upvotes: 1

Related Questions