CODEWITHSUNDEEP
mysql
Raptor
Raptor

Reputation: 54212

Sorting Leaderboard in MySQL

I use the following SQL to order scores by rank in my leaderboard table:

SELECT score, 1+(SELECT COUNT(*) FROM leaderboard a WHERE a.score > b.score) AS rank 
FROM leaderboard b 
WHERE stage=1 
GROUP BY id

where my table schema is like this:

CREATE TABLE `leaderboard` (
  `auto_id` int(11) NOT NULL AUTO_INCREMENT,
  `score` int(11) NOT NULL DEFAULT '0',
  `id` int(11) NOT NULL,
  `created_on` datetime NOT NULL,
  PRIMARY KEY (`auto_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

Some sample data rows are as follow:

auto_id     score      id  created_on
====================================================
1,          72023456,   1, '2014-12-30 11:49:59'
2,          1420234,    1, '2014-12-30 12:00:21'
3,          420234,     1, '2014-12-30 12:00:38'
4,          16382,      1, '2014-12-30 16:31:12'
5,          16382,      1, '2014-12-30 16:34:18'
6,          16382,      1, '2014-12-30 16:37:43'
7,          17713,      1, '2014-12-30 16:38:35'
8,          17257,      1, '2014-12-30 18:53:45'
9,          10625,      1, '2014-12-30 18:58:10'
10,         17272,      1, '2014-12-30 18:58:59'
11,         17328,      1, '2014-12-30 18:59:44'
12,         17267,      37, '2015-01-02 17:11:59'
13,         16267,      37, '2015-01-02 17:12:30'
14,         16267,      37, '2015-01-02 17:13:02'
15,         35509,      37, '2015-01-02 17:17:46'
16,         18286,      37, '2015-01-02 18:20:09'
17,         16279,      37, '2015-01-02 18:20:43'
18,         16264,      37, '2015-01-02 18:21:15'
19,         16265,      37, '2015-01-02 18:40:04'

Since id is player's ID, I have to GROUP BY id. It gives the following result:

id   score        rank
=========================
1    72023456     1
37   17267        11

How can I obtain the following expected results?

id   score        rank
=========================
1    72023456     1
37   35509        2

The current problem is, the existing result is not the MAX score of the player.

Bonus: My ultimate goal is to get the entries 1 rank higher & 1 rank lower than specific id.

Upvotes: 2

Views: 284

Answers (2)

Jorge Campos
Jorge Campos

Reputation: 23361

As MySql does not have Windowing Functions the query that you need has to mimic its behavior, so you have to use variables.

select id, score, @rank :=@rank+1 as rank
  from (
         SELECT b.id, max(b.score) as score
           FROM leaderboard b
          GROUP BY id
          order by score desc
        ) tab
        ,(select @rank := 0) r

EDIT: I made a little mistake. I've corrected it now.

The output will be:

id   score        rank
=========================
1    72023456     1
37   35509        2

Basically what I'm doing is creating an iterator on the query and for every row it will increment the variable. As I added the order by it will rank your values based on that order by. But that rank has to happen outside the query because the order be alongside with the rank will mess things up if there is more than two IDs

I will edit the query with the solution for "1 rank higher & 1 rank lower than specific id."

EDIT: for the bonus (not pretty though)

select id, score, rank
from (
   select tab.id, tab.score, @rank :=@rank+1 as rank
     from (select @rank := 0) r,
            (SELECT b.id, max(b.score) as score
            FROM leaderboard b
            GROUP BY id
            order by score desc) tab
) spec
where spec.id=2
UNION
select id, score, rank
from (
   select tab.id, tab.score, @rank :=@rank+1 as rank
     from (select @rank := 0) r,
            (SELECT b.id, max(b.score) as score
            FROM leaderboard b
            GROUP BY id
            order by score desc) tab
) spec
where spec.rank=
(select rank-1
from (
   select tab.id, tab.score, @rank :=@rank+1 as rank
     from (select @rank := 0) r,
            (SELECT b.id, max(b.score) as score
            FROM leaderboard b
            GROUP BY id
            order by score desc) tab
) spec
where spec.id=2)
UNION
select id, score, rank
from (
   select tab.id, tab.score, @rank :=@rank+1 as rank
     from (select @rank := 0) r,
            (SELECT b.id, max(b.score) as score
            FROM leaderboard b
            GROUP BY id
            order by score desc) tab
) spec
where spec.rank=
(select rank+1
from (
   select tab.id, tab.score, @rank :=@rank+1 as rank
     from (select @rank := 0) r,
            (SELECT b.id, max(b.score) as score
            FROM leaderboard b
            GROUP BY id
            order by score desc) tab
) spec
where spec.id=2)
order by rank;

Note that you put the specific ID on the clauses spec.id=2 (I've put 2 because I had to change the values on my enviroment to test it)

Here the SQL Fiddle with my test with the two queries working: http://sqlfiddle.com/#!2/75047/2

Upvotes: 2

Chris Bouchard
Chris Bouchard

Reputation: 805

The reason the score isn't the max is that, since score isn't in the GROUP BY clause, MySQL is just picking the first value as a representative. Technically, this isn't valid SQL. You probably want to use MAX(score) AS score.

As for the rank, since MySQL doesn't support window functions you'll have to hack something yourself. You can look at this SO post for more info. The standard ways seem to be to use mutable variables to count the rows, or to join the query to itself using an inequality in the ON clause. Neither seems very elegant.

Upvotes: 0

Related Questions