# Rank students based on their average in MySQL

I have been researching and trying various queries to get this to work. What I want to happen is to rank each student based on their average which will be calculated by using AVG(grade) and this will be grouped by student_id. The table with the necessary info is as follows:

assessment_id | student_id | grade | subject_id


What I would like to see after the query is:

student_id | AVG(grade) | rank


I would also like to get the rank of a student using PHP, for example (mysql_query) WHERE student_id = '1001'

The closest I have come to getting this was by using the query below:

SELECT student_id, AVG(grade), (@rn := @rn + 1) AS rank
FROM grades CROSS JOIN (SELECT @rn := 0) CONST
GROUP BY student_id ORDER BY AVG(grade) DESC


The problem with the query above is that it ranks the students based on their student_id number.

I have searched for solutions all over but they just seem to not solve what I am after. I would really appreciate your help towards this.

I can't test this right now, but you probably want something like this:

SELECT student_id, avg, (@rn := @rn + 1) AS rank
FROM

• @mustaccio it can probably be solved with modifying the (@rn := @rn + 1) to use a CASE expression or the IF function (and possibly with the help of an additional variable). – ypercubeᵀᴹ Jul 16 '16 at 11:51