1

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.

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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