Artful Common Queries

Rank order

from the Artful Common Queries page

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);

Windowing functions, long available in other RDBMSs, came to MySQL in version 8 and to MariaDB in version 10.2. They make simple work of querying rank order:

SELECT name, votes, rank() OVER(win) AS 'Vote Rank'
FROM votes
WINDOW win AS (ORDER BY votes); 
+-------+-------+-----------+
| name  | votes | Vote Rank |
+-------+-------+-----------+
| Smith |    10 |         1 |
| Jones |    15 |         2 |
| White |    20 |         3 |
| Brown |    20 |         3 |
| Black |    40 |         5 |
| Green |    50 |         6 |
+-------+-------+-----------+

Before MySQL 8 and MariaDB 10.2, 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(N²) 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. 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, 
    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;

Last updated 17 Dec 2024