CODEWITHSUNDEEP
mysqlsqlranking
Anar Bayramov
Anar Bayramov

Reputation: 11584

Rank students depending on their class

I am trying to rank my students depending on their class (exam_place_id) by their points.

This is my database enter image description here

For example student with highest point in exam_place_id 11 will be ranked as 1 highest point in exam_place_id 12 will be ranked 1 as well

overall ranking is their score in overall

I've done overall ranking by

select er.*,
      (@rank := @rank + 1)       as ranking
from examresults er cross join
     (select @rank := 0, @point := -1) params

order by point desc;

however when I try 

select er.*,
      (@rank := @rank + 1)       as ranking
from examresults er cross join
     (select @rank := 0, @point := -1) params
**group by exam_place_id** 
order by point desc;

it does not work.

Note : I also want to update my class_ranking column with the values but my current query does select.

Upvotes: 2

Views: 106

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269733

You need to keep track of the exam places and use this for the ranking:

select er.*,
       (@rank := if(@ep = exam_place_id, @rank + 1,
                    if(@ep := exam_place_id, 1, 1) 
                   )
       ) as ranking
from examresults er cross join
     (select @rank := 0, @ep := -1) params
order by exam_place_id,
         point desc;

Upvotes: 1

Related Questions