CODEWITHSUNDEEP
mysqlselectgroup-byranking
Zia
Zia

Reputation: 191

Dynamic Get Rank By Mysql Query

I have a result table like:


ID  STUDENT_ID  Branch_id   Class_id    Exam_id Subject_id  Numbers     Date
1       653         5           1           1   8               60      2012-01-01
2       653         5           1           1   9               40      2012-01-01
3       653         5           1           1   10              80      2012-01-01
4       653         5           1           1   11              50      2012-01-01
5       653         5           1           1   12              65      2012-01-01
6       653         5           1           1   13              33      2012-01-01
7       653         5           1           1   15              86      2012-01-01
8       222         5           1           1   8               100     2012-01-01
9       222         5           1           1   9               80      2012-01-01
10      222         5           1           1   10              92      2012-01-01
11      222         5           1           1   11              50      2012-01-01
12      222         5           1           1   12              65      2012-01-01
13      222         5           1           1   13              33      2012-01-01
7       222         5           1           1   15              86      2012-01-01

My Desire Result like:


Student_ID  Math    English      Science    Total   Rank
1       90  89      88  267  1
2       90  89      88  267  1
3       58  45      98  201      2

I want to get student rank by this method:

Reference Link

SET @rank = 0, @prev_val = NULL;

SELECT rank, correct FROM 
(
    SELECT 
        @rank := IF(@prev_val=correct,@rank,@rank+1) AS rank,
        @prev_val := correct AS correct, uid
    FROM quiz_user 
    ORDER BY correct DESC
)as result WHERE uid=xxxxxxxxxxxx

This query what I need only difference between table structure the author of post assign a rank on correct column and I need to assign rank on numbers SUM(numbers) column after sum all numbers.

Please Help.

Upvotes: 1

Views: 1109

Answers (1)

Saharsh Shah
Saharsh Shah

Reputation: 29051

Try this: 

SELECT STUDENT_ID, Numbers, IF(@marks=(@marks:=Numbers), @auto, @auto:=@auto+1) rank 
FROM (SELECT STUDENT_ID, SUM(Numbers) Numbers
      FROM quiz_user 
      GROUP BY STUDENT_ID 
      ORDER BY Numbers DESC, STUDENT_ID 
     ) AS A, (SELECT @auto:=0, @marks:=0) AS B;

Upvotes: 1

Related Questions