In this article you’ll learn how to get user’s rank in mysql query. Check the link http://thinkdiff.net/mysql/how-to-get-rank-using-mysql-query
Some days ago I was working in a quiz project. Where user will play quiz and for each correct answer they will earn points. One of the task of this quiz application was, to get rank of a particular user. Now I am showing how could I solve this problem using mysql query.
Here is the table structure:
CREATE TABLE IF NOT EXISTS `quiz_user` ( `uid` BIGINT UNSIGNED NOT NULL , `participated` SMALLINT UNSIGNED NULL DEFAULT 0 , `correct` SMALLINT UNSIGNED NULL DEFAULT 0 , `wrong` SMALLINT UNSIGNED NULL DEFAULT 0 , `created` DATETIME NULL , `updated` DATETIME NULL , PRIMARY KEY (`uid`) ) ENGINE = InnoDB
This table contains user records of quiz game. Here is an examples:
I just read SQL: Ranking without self join, in which
Noach shares a nice MySQL-specific trick based on user-defined variables to compute rankings.
Shlomi's trick reminds me somewhat of the trick I came across little over a year ago to caclulate percentiles. At that time, several people pointed out to me too that using user-defined variables in this way can be unreliable.The problem with user-defined variablesSo what is the problem exaclty? Well, whenever a query assigns to a variable, and that same variable is read in another part of the query, you're on thin ice. That's because the …