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
But can't get the desired output. Any suggestion or help is appreciated.
3 Answers
Answers 1
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 Answers 2
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!
Answers 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 
0 comments:
Post a Comment