Rank order

-- ---------------------------------------------------------------------------
-- Rank order

-- from the Artful Common Queries page
-- http://www.artfulsoftware.com/infotree/qrytip.php?id=460
-- ---------------------------------------------------------------------------

-- Without MSSQL's RANK() aggregate function, how do we display rank order in a
-- MySQL query, for example from a table like this?

  CREATE TABLE votes (NAME CHAR(10), votes INT);
  INSERT INTO votes
  VALUES
    ('Smith', 10),
    ('Jones', 15),
    ('White', 20),
    ('Black', 40),
    ('Green', 50),
    ('Brown', 20);

-- The query is a two-step:
-- 1. Join the table to itself on the value to be ranked, handling ties
-- 2. Group and order the result of the self-join on rank:

  SELECT
    v1. NAME,
    v1.votes,
    COUNT(v2.votes) AS Rank
  FROM
    votes v1
  JOIN votes v2 ON v1.votes < v2.votes
  OR (
    v1.votes = v2.votes
    AND v1. NAME = v2. NAME
  )
  GROUP BY
    v1. NAME,
    v1.votes
  ORDER BY
    v1.votes DESC,
    v1. NAME DESC;

-- +-------+-------+------+
-- | name  | votes | Rank |
-- +-------+-------+------+
-- | Green |    50 |    1 |
-- | Black |    40 |    2 |
-- | White |    20 |    3 |
-- | Brown |    20 |    3 |
-- | Jones |    15 |    5 |
-- | Smith |    10 |    6 |
-- +-------+-------+------+

-- Suppose you want to know the vote count for White and the names and
-- tallies for the next highest and next lowest counts:

  SELECT
    s1.name, s1.votes, COUNT(s2.name) rank,
    IF(s1.name = 'White','<-',' ') AS 'Near Ranks'
  FROM votes s1
  JOIN votes s2 ON s1.votes < s2.votes or (s1.votes=s2.votes and s1.name = s2.name)
  GROUP BY s1.name, s1.votes
  ORDER BY rank
  ) a
  LEFT JOIN (
    SELECT
      s1.name, s1.votes, COUNT(s2.name) rank,
      IF(s1.name = 'White', '<-',' ') AS 'Near Ranks'
    FROM votes s1
    JOIN votes s2 ON s1.votes < s2.votes or (s1.votes=s2.votes and s1.name = s2.name)
    GROUP BY s1.name, s1.votes
    ORDER BY rank
  ) b ON a.rank BETWEEN b.rank-1 AND b.rank+1
  WHERE a.name = 'White';

-- +-------+-------+------+------------+
-- | name  | votes | rank | Near Ranks |
-- +-------+-------+------+------------+
-- | Black |    40 |    2 |            |
-- | White |    20 |    3 | <-         |
-- | Brown |    20 |    3 |            |
-- +-------+-------+------+------------+

-- But these methods don't scale; they're O(N2) or worse, especially if
-- non-trivial calculation is needed to compute rank. If you do not need
-- to identify ties in the ranking column, writing a temporary ordering
-- table and then querying it is often much faster:

  DROP TEMPORARY TABLE
  IF EXISTS tmp;

  SET @i = 0;

  CREATE TEMPORARY TABLE tmp SELECT
    idcol,
    valuecol ,@i :=@i + 1 AS rank
  ORDER BY
    valuecol DESC;

-- Baron Schwartz has a useful page on optimising rankings for leaderboards.
-- (http://onlamp.com/pub/a/mysql/2007/03/01/optimize-mysql-rank-data.html)
-- It requires maintenance of a ranking computation table, as described on
-- his page under "How to maintain the rank column".
-- That table has this structure:

  CREATE TABLE score_ranked (
    gamer INT NOT NULL,
    game INT NOT NULL,
    score INT
  ) NOT NULL,
   rank_in_game INT NOT NULL DEFAULT 0,
   PRIMARY KEY (gamer, game),
   KEY (game, score),
   KEY (score),
   KEY (game, rank_in_game)
  ) ENGINE = INNODB;

-- If you use Baron's schema, you will find the following query, developed
-- on a MySQL forum by Rick James, reasonably efficient for retrieving top
-- score sums for all games:

SELECT
  @seq := 0, -- raw 1,2,3,...
  @rank := 0, -- allow for ties
  @prev := 999999999; -- catch ties
SELECT Rank, gamer, point
FROM
  (
    SELECT
      @seq :=
    IF (@prev = point, @seq, @seq + 1) AS seq,
    @rank :=
  IF (@prev = point, @rank, @seq) AS Rank,
  @prev := point AS prev, # I forgot this
  gamer,
  point
FROM
  (
    SELECT gamer, SUM(score) AS point
    FROM
      score_ranked s
    GROUP BY
      gamer
    ORDER BY
      point DESC
  ) x
  ) y
WHERE
  Rank <= 10
ORDER BY
  Rank, gamer;