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
 (SELECT student_id, AVG(grade) as avg
  FROM grades
  GROUP BY student_id ORDER BY AVG(grade) DESC) agg
CROSS JOIN (SELECT @rn := 0) CONST
ORDER BY avg DESC