Mysql Group By Levels ranking

Question

rank    points  player_id   quiz_id
 1       88        1          40
 2       80        3          40
 3       30        3          41
 4       20        1          41

Getting this output from the following query:

SELECT m.rank,
       m.scorer AS points,
       m.player_id
FROM
  ( SELECT d.player_id,
           d.scorer, @rownum := @rownum + 1 AS rank
   FROM
     ( SELECT t.player_id,
              SUM(t.score) AS scorer
      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 = 1
        AND profile.signin_source_id != 1
        AND profile.is_active = 1
        AND t.quiz_id IN (1,
                          2)
      GROUP BY t.player_id
      ORDER BY scorer DESC, t.created_utc ASC) d,

     (SELECT @rownum := 0) r) m
WHERE m.scorer > 0

However, the output I want is rank for each level separated out.

rank    points  player_id   quiz_id
 1       88        1          40
 2       80        3          40
 1       30        3          41
 2       20        1          41

I followed these :

How to perform grouped ranking in MySQL

https://blog.sqlauthority.com/2014/03/09/mysql-reset-row-number-for-each-group-partition-by-row-number/

But can't get the desired output. Any suggestion or help is appreciated.

What MySQL version are you using? – Mureinik Aug 12 '18 at 16:43 — Mureinik, Aug 12 '18 at 16:43
5.7 is the MySQL version. – Kinshuk Lahiri Aug 13 '18 at 04:57 — Kinshuk Lahiri, Aug 13 '18 at 04:57

Michał Turczyn · Answer 1 · 2018-08-18T07:28:03.560

Try this query, it is simplier IMO:

select @quiz_id_lag := 0, @rank := 1;

select rank, points, player_id, quiz_id from (
  select points,
         player_id,
         case when @quiz_id_lag = quiz_id then @rank := @rank + 1 else @rank := 1 end rank,
         @quiz_id_lag,
         @quiz_id_lag := quiz_id,
         quiz_id
  from tbl
  order by quiz_id, points desc
) a;

To incorporate this in your query, try:

SELECT @quiz_id_lag := 0, @rank := 1;

SELECT rank,
       scorer AS points,
       player_id
FROM (
    SELECT quiz_id,
           player_id,
           scorer,
           CASE WHEN @quiz_id_lag = quiz_id THEN @rank := @rank + 1 ELSE @rank := 1 END rank,
           @quiz_id_lag := quiz_id,
           quiz_id
    FROM ( 
         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 = 1 AND profile.signin_source_id != 1
           AND profile.is_active = 1 AND t.quiz_id IN (1, 2)
        GROUP BY t.player_id
    ) d
    WHERE scorer > 0
    ORDER BY quiz_id, scorer DESC
) m

@KinshukLahiri I think you need to alter `WHERE` clause in the inner query to get desired result: `WHERE t.is_active = 1 AND quiz.contest_id = 1 AND profile.signin_source_id != 1 AND profile.is_active = 1 AND t.quiz_id IN (1, 2)` — Michał Turczyn, Aug 19 '18 at 21:01

Kinshuk Lahiri · Accepted Answer · 2018-08-13T06:47:00.200

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!

score 3 · Answer 3 · answered Aug 13 '18 at 07:11

3

Try this

SELECT m.rank,m.scorer AS points,m.player_id, m.quiz_id 
FROM (
    SELECT d.player_id,d.quiz_id, d.scorer,  
    @cur:= IF(quiz_id=@id, @cur+1, 1) AS rank, 
    @id := quiz_id 
    FROM (
            SELECT t.player_id, quiz.id as quiz_id, SUM(t.score) as scorer
            FROM answers t JOIN profile ON profile.player_id = t.player_id
                    JOIN quiz ON t.quiz_id = quize.id  
            WHERE t.is_active = 1 AND quiz.contest_id = 1
                    AND profile.signin_source_id != 1 AND profile.is_active = 1
                    AND t.quiz_id IN (1,2) 
            GROUP BY t.player_id, quiz.id 
            ORDER BY scorer DESC
    ) d, (SELECT @id:=(SELECT MIN(id) FROM quiz), @cur:=0) AS init
    order by d.quiz_id, d.scorer desc) m 
WHERE m.scorer > 0

answered Aug 13 '18 at 07:11

Gaj

888
5
5

Thanks for the input, however, the ranking that I get is wrong. – Kinshuk Lahiri Aug 13 '18 at 07:24
This is the row returned for quiz_id 1 1008 55 529 1 However, the rank is 893 for that particular player. – Kinshuk Lahiri Aug 13 '18 at 07:48
It may the reason of "m.scorer > 0", so if negative score not applicable then move that m.scorer > 0 condition to inner most select statement where clause as t.score > 0 – Gaj Aug 13 '18 at 07:55

Mysql Group By Levels ranking

3 Answers3