Geek Answers Handbook

Mysql Get rating with filters

I have a label table, for example:

  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

I need to know the rank of a student if one or more students are equal in rank. and a filter with the identifier branc and class.

Can anyone help me?

+4
source share
2 answers

Try the following:

SELECT STUDENT_ID, Numbers, IF(@marks=(@marks:=Numbers), @auto, @auto: =@auto +1) rank FROM (SELECT STUDENT_ID, Branch_id, Class_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 WHERE Branch_id = 5 AND Class_id = 1;
+2
source 
 select sum(Numbers),STUDENT_ID,Branch_id, Class_id from marksheet where Branch_id='value' and Class_id='valueClass' group by STUDENT_ID,Branch_id, Class_id order by sum(Numbers) desc

demo script here

0
source

More articles:


All Articles