1

Imagine there is a table with users logins and their scores:

+----+-------+-------+
| id | login | score |
+----+-------+-------+
| 1  | log1  | 4     |
+----+-------+-------+
| 2  | log2  | 4     |
+----+-------+-------+
| 3  | log3  | 28    |
+----+-------+-------+
| 4  | log4  | 1     |
+----+-------+-------+
| 5  | log5  | 3     |
+----+-------+-------+

How to select the rank of a specific user (for example: "log2")?

The very important thing is that this selected rank shoud increase even in case of equal scores so every user will have a unique rank.

Example of how rank shoud be counted:

+----+-------+-------+------+
| id | login | score | rank |
+----+-------+-------+------+
| 1  | log1  | 4     | 2    |
+----+-------+-------+------+
| 2  | log2  | 4     | 3    |
+----+-------+-------+------+
| 3  | log3  | 28    | 1    |
+----+-------+-------+------+
| 4  | log4  | 1     | 5    |
+----+-------+-------+------+
| 5  | log5  | 3     | 4    |
+----+-------+-------+------+

Example of output:

+----+-------+-------+------+
| id | login | score | rank |
+----+-------+-------+------+
| 2  | log2  | 4     | 3    |
+----+-------+-------+------+
1
  • Get MariaDB 10.2 and use its "Windowing functions", one of which is designed for exactly this.
    – Rick James
    Feb 19, 2017 at 4:25

1 Answer 1

1

This is possible with ORDER BY score DESC and user variable.

Create table/insert data

CREATE TABLE logins
    (`id` INT, `login` VARCHAR(4), `score` INT)
;

INSERT INTO logins
    (`id`, `login`, `score`)
VALUES
    (1, 'log1', 4),
    (2, 'log2', 4),
    (3, 'log3', 28),
    (4, 'log4', 1),
    (5, 'log5', 3)
;

Query

With ORDER BY score DESC and user variable this also counts the same score up.

 SELECT 
   *
   , (@rank := @rank + 1) AS rank
  FROM 
   logins
  CROSS JOIN( 
   SELECT
     @rank := 0
   )  
   AS 
    init_var_var
  ORDER BY
   logins.score DESC 

Result

   id  login    score  @rank := 0    rank  
------  ------  ------  ----------  --------
     3  log3        28           0         1
     1  log1         4           0         2
     2  log2         4           0         3
     5  log5         3           0         4
     4  log4         1           0         5

Query

As delivered table so you can filter out one record.

SELECT
   id
 , login
 , score
 , rank
FROM ( 
  SELECT 
   *
   , (@rank := @rank + 1) AS rank
  FROM 
   logins
  CROSS JOIN( 
   SELECT
     @rank := 0
   )  
   AS 
    init_var_var
  ORDER BY
   logins.score DESC  
)
 AS logins_ordered_ranked
WHERE
  id = 2

Result

    id  login    score    rank  
------  ------  ------  --------
     2  log2         4         3

Query without WHERE id=2

SELECT
   id
 , login
 , score
 , rank
FROM ( 
  SELECT 
   *
   , (@rank := @rank + 1) AS rank
  FROM 
   logins
  CROSS JOIN( 
   SELECT
     @rank := 0
   )  
   AS 
    init_var_var
  ORDER BY
   logins.score DESC  
)
 AS logins_ordered_ranked

Result

    id  login    score    rank  
------  ------  ------  --------
     3  log3        28         1
     1  log1         4         2
     2  log2         4         3
     5  log5         3         4
     4  log4         1         5
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.