Mysql – Rank students based on their average in MySQL

group byMySQLrank

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.

Best Answer

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

Related Solutions

MYSQL score by rank

Using the techniques described here to rank items in MySQL, you can calculate the scores you need using a derived table and some hackery with user variables.

Here's your table with the sample data you provided:

CREATE TABLE blah (
    id INT
  , value INT
);

INSERT blah (id, value)
VALUES 
    (1, 3)
  , (2, 5)
  , (3, 2)
  , (4, 5)
;

And here's the query you need:

-- find the min and max ranks for later use
SELECT
    @min_rank := 1 AS min_rank
  , @max_rank := (SELECT COUNT(DISTINCT value) FROM blah) AS max_rank
;

-- use the calculated min and max ranks to calculate score
SELECT
    id
  , value
  , rank
  , 10.0 * (@max_rank - rank) / (@max_rank - @min_rank) AS score
FROM (
  SELECT
      id
    , value
    , @curr_rank := IF(@prev_rank = value, @curr_rank, @curr_rank + 1) AS rank
    , @prev_rank := value
  FROM blah, (SELECT @curr_rank := 0) r, (SELECT @prev_rank := NULL) p
  ORDER BY value DESC
) ranked_blah;

The output I get for the second query on SQL Fiddle is as follows:

| ID | VALUE | RANK | SCORE |
-----------------------------
|  2 |     5 |    1 |    10 |
|  4 |     5 |    1 |    10 |
|  1 |     3 |    2 |     5 |
|  3 |     2 |    3 |     0 |

Note that this approach will work best if you have an index on value. This will let you find @max_rank quickly using the COUNT technique @ypercube described.

Mysql – Best database design for website ranking

Will you be ranking the billions of web sites? If so, then on-the-fly and recompute-all are out of the question.

Instead, have a background task working on the problem. For display, show the info that is in the table, possibly out of date by a day or two (depending on how long it takes to recalc).

Put the ranking in a separate table, with only website_id and rank. This will make replacing the entire table after the recalc atomic and fast. Perhaps a second table with website_id, country_code (CHAR(2) CHARACTER SET ascii), and rank.

You have not explained how the ranking is calculated, so I can't give you any insight in how to do it as a background task.

Best Answer

Related Solutions

MYSQL score by rank

Mysql – Best database design for website ranking

Related Question