Mysql – How to get overall rank based on data regardless of scope (all items, user specific, etc.)

MySQLrank

Table schemas:

servers
- id
- user_id
- game_id
- slug
  ...

server_votes
- id
- server_id
- ip_address
- created_at
  ...

So, I have a two tables: servers and server_votes, and I want to get the rank of each server based on how many votes each server has received in a certain amount of time. I have created the following query to do just that (apart from the ranking part):

SELECT 
    s.id,
    s.user_id,
    s.slug,
    (
        SELECT 
            COUNT(sv.id)
        FROM
            server_votes AS sv
        WHERE
            DATE(sv.created_at) > '2016-01-24 19:11:15'
        AND 
            sv.server_id = s.id
    ) AS votes
FROM
    servers AS s
LEFT JOIN
    server_votes AS sv 
ON 
    sv.server_id = s.id
WHERE 
    s.game_id = 1
GROUP BY
    s.id
ORDER BY 
    votes DESC,
    s.id ASC

The query above will return the following:

*----*---------*---------*-------*
| id | user_id | slug    | votes |
*----*---------*---------*-------*
| 4  | 1       | Server4 | 3     |
| 2  | 1       | Server2 | 2     |
| 1  | 1       | Server1 | 0     |
| 3  | 2       | Server3 | 0     |
| 5  | 2       | Server5 | 0     |
*----*---------*---------*-------*

I'd like to end up with the following:

*----*---------*---------*-------*------*
| id | user_id | slug    | votes | rank |
*----*---------*---------*-------*------*
| 4  | 1       | Server4 | 3     | 1    |
| 2  | 1       | Server2 | 2     | 2    |
| 1  | 1       | Server1 | 0     | 3    |
| 3  | 2       | Server3 | 0     | 4    |
| 5  | 2       | Server5 | 0     | 5    |
*----*---------*---------*-------*------*

Or if I'm querying a specific user's server's, I'd get the rank in context of all servers:

*----*---------*---------*-------*------*
| id | user_id | slug    | votes | rank |
*----*---------*---------*-------*------*
| 3  | 2       | Server3 | 0     | 4    |
| 5  | 2       | Server5 | 0     | 5    |
*----*---------*---------*-------*------*

Any insight would be appreciated.

Also, here's the SQL Fiddle incase that is of any help to you.

Best Answer

This query uses a variable for ranking:

SELECT t.id,
    t.user_id,
    t.slug,
    t.votes,
    @rank := @rank + 1 AS rank
    FROM (
    SELECT 
        s.id,
        s.user_id,
        s.slug,
        (
            SELECT 
                COUNT(sv.id)
            FROM
                server_votes AS sv
            WHERE
                DATE(sv.created_at) > '2016-01-24 19:11:15'
            AND 
                sv.server_id = s.id
        ) AS votes
    FROM
        servers AS s
    LEFT JOIN
        server_votes AS sv 
    ON 
        sv.server_id = s.id
    WHERE 
        s.game_id = 1
    GROUP BY s.id
) t, (SELECT @rank := 0) r
ORDER BY 
    t.votes DESC,
    t.id ASC

See SQL Fiddle.

Output:

id  user_id slug    votes   rank
4   1       Server4 3       1
2   1       Server2 2       2
1   1       Server1 0       3
3   2       Server3 0       4
5   2       Server5 0       5

Once the above query is put in a subquery with a WHERE clause, it outputs data for a specific user:

SELECT 
    r.id,
    r.user_id,
    r.slug,
    r.votes,
    r.rank
FROM (
   ...
) r
WHERE r.user_id = 2;

It will output id 3 and 5.

See SQL Fiddle

Related Solutions

Mysql – Get the rank of a user in a score table

SELECT id, name, score, FIND_IN_SET( score, (
SELECT GROUP_CONCAT( score
ORDER BY score DESC ) 
FROM scores )
) AS rank
FROM scores

gives this list:

id name  score rank
1  Ida   100   2
2  Boo    58   5
3  Lala   88   4
4  Bash  102   1
5  Assem  99   3

Getting a single person score:

SELECT id, name, score, FIND_IN_SET( score, (    
SELECT GROUP_CONCAT( score
ORDER BY score DESC ) 
FROM scores )
) AS rank
FROM scores
WHERE name =  'Assem'

Gives this result:

id name score rank
5 Assem 99 3

You'll have one scan to get the score list, and another scan or seek to do something useful with it. An index on the score column would help performance on large tables.

Mysql – Get user rank handling equal scores

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

Best Answer

Related Solutions

Mysql – Get the rank of a user in a score table

Mysql – Get user rank handling equal scores

Related Question