How to Perform Grouped Ranking in MySQL

How to perform grouped ranking in MySQL

SELECT id_student, id_class, grade,
   @student:=CASE WHEN @class <> id_class THEN 0 ELSE @student+1 END AS rn,
   @class:=id_class AS clset
FROM
  (SELECT @student:= -1) s,
  (SELECT @class:= -1) c,
  (SELECT *
   FROM mytable
   ORDER BY id_class, id_student
  ) t

This works in a very plain way:

1. Initial query is ordered by id_class first, id_student second.
2. @student and @class are initialized to -1
3. @class is used to test if the next set is entered. If the previous value of the id_class (which is stored in @class) is not equal to the current value (which is stored in id_class), the @student is zeroed. Otherwise is is incremented.
4. @class is assigned with the new value of id_class, and it will be used in test on step 3 at the next row.

Mysql Ranking within grouped results

You're almost there, use another variable to compute group:

SELECT @ss := CASE WHEN @grp = sem_id THEN @ss + 1 ELSE 1 END AS rank, sem_id, result_month, @grp := sem_id
FROM   (select * from xx_table ORDER  BY sem_id, result_month DESC) m
CROSS JOIN (SELECT @ss := 0, @grp = null) ss 

See demo here.

SQL Rank Function with Group

Use a subquery for the aggregation and ordering:

SELECT id, sum_points, @rank := @rank + 1 AS rank
FROM (SELECT g.id, SUM(s.points) AS sum_points
      FROM groups g LEFT JOIN
           user_group ug
           ON g.id = ug.clan LEFT JOIN
           stats s
           ON ug.user = s.id
      GROUP BY g.id
      ORDER BY sum_points DESC
     ) s CROSS JOIN
     (SELECT @rank := 0) params;

This has been an issue in MySQL for a while -- variables don't work well with aggregation and ordering.

Note that in MySQL 8+, this is much more simply written as:

SELECT g.id, SUM(s.points) AS sum_points,
       ROW_NUMBER() OVER (ORDER BY SUM(s.points) DESC) as rank
FROM groups g LEFT JOIN
     user_group ug
     ON g.id = ug.clan LEFT JOIN
     stats s
     ON ug.user = s.id
GROUP BY g.id

Mysql Group By Levels ranking

Finally got the desired result, ending up with the query, in order to get proper ranking for level wise:

SELECT m.rank,m.scorer AS points,m.player_id, m.quiz_id  FROM (
 SELECT d.player_id,d.scorer, 
    @rownum:= CASE WHEN @quiz_id <> d.quiz_id THEN 1 ELSE @rownum+1 END as rank, 
    @quiz_id:= d.quiz_id as quiz_id FROM 
    (SELECT @rownum := 1) r,
    (SELECT @quiz_id := 0) c,(
    SELECT t.player_id,SUM(t.score) as scorer, t.quiz_id 
    FROM answers t JOIN profile ON 
    profile.player_id = t.player_id
    JOIN quiz ON t.quiz_id = quiz.id
    WHERE t.is_active = 1 AND quiz.contest_id = 2 AND 
     profile.signin_source_id != 1 AND profile.is_active = 1
     GROUP BY t.player_id,t.quiz_id 
     ORDER BY quiz_id DESC,scorer DESC, t.created_utc ASC) d
   ) m 
WHERE m.scorer > 0 ORDER BY quiz_id

This will give the entire result set for all the levels for a specific group, if want to get the rank for specific levels from a specific group, then do add

AND t.quiz_id IN (1,2)  

Thanks to all who ever participated!

mysql get ranking position grouped with joined table

You want rank() or row_number():

select p.*,
       rank() over (partition by player_game_id order by player_score desc) as rank
from players
order by player_game_id, player_score desc;

If two players have the same score, then rank() gives them the same ranking. row_number() will arbitrarily assign them adjacent rankings.

The above works on MySQL 8+. In earlier versions you have two options -- subqueries and variables. Here is an example using a correlated subquery:

select p.*,
       (select 1 + count(*)
        from players p2
        where p2.player_game_id = p.player_game_id and
              p2.player_score > p.player_score
       ) as ranking
from players
order by player_game_id, player_score desc;

This is not as efficient as rank(). But with an index on players(player_game_id, player_score) and not too many players per game, then this should have reasonable performance.

MySQL ranking with GROUP BY and SUM

Reminding the OP's question:

I would like to be able to get the rank of a given user_id.

In order to actually perform operations over the @rank you have to user another derived table (yes, it is inefficient, that's why it is better not to handle this in MySQL):

SELECT * FROM (
  SELECT s.*, @rank := @rank + 1 rank FROM (
    SELECT user_id, sum(points) TotalPoints FROM t
    GROUP BY user_id
  ) s, (SELECT @rank := 0) init
  ORDER BY TotalPoints DESC
) r
WHERE user_id = 3

Output

| USER_ID | TOTALPOINTS | RANK |
|---------|-------------|------|
|       3 |          25 |    2 |

The process is basically:

1. Get the total amounts of points per user
2. Sort by those total points ranking
3. Filter once you have the rank (otherwise, the rank will be compromised)

Fiddle here.

Group and Rank Rows in Mysql

Thanks for your help guys.
I was able to come up with an answer based on the following Query:

$totalQuery = "SELECT SUM(track_length) as usertracklength, username, MAX(track_create_time) as lasttrack, count(DISTINCT track_create_time) as totaldays FROM user_tracks GROUP BY username ORDER BY usertracklength DESC";
$totalResult = mysql_query($totalQuery);
$rankResult = mysql_query($totalQuery);
$totalNumEntries = mysql_num_rows($totalResult);

Then Ouputting that to an array

// rank position array
$rankArray = array();

while ($row1 = mysql_fetch_array($rankResult)) {
    $rankArray[] = $row1['username'];
}

Then finding position of that username in the array by using a foreach in php

foreach ($rankArray as $rank => $user) {
    if ($user == $username) {
    $yourRank = $rank+1; 
    }
}

It's the long way around, but I suppose it works for what I'm going for.
Was kind of hoping to get it done within the mysql query for efficiency.

Thanks!

---

Related Topics