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 
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 
 (SELECT student_id, AVG(grade) as avg
  FROM grades
  GROUP BY student_id ORDER BY AVG(grade) DESC) agg
  • Hope Im not asking too much but is there a way to assign the same rank to averages that matches and then the following student will be ranked at the row number? For example A and B scored 95 so both rank at 4 and then C with a score of 93 will rank at 6. – DBeck Jul 15 '16 at 20:48
  • You should probably post it as a new question then. I don't see an easy way of doing that; it might involve calculating the maximum grade of all students in a separate subquery, then computing ranking buckets... Please consider including the table DDL and insert statements with sample data -- this will help those who might try answering. – mustaccio Jul 15 '16 at 21:01
  • @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
SELECT student_id, AVG(grade), RANK() OVER (ORDER BY AVG(grade) DESC) AS rrank
FROM grades
GROUP BY student_id
  • Welcome on the DBA SE! Please give also some explanation, how your query works. – peterh - Reinstate Monica Apr 2 '20 at 17:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.