Rank order -- Without MSSQL's RANK() aggregate function, how do we display rank order in a MySQL query

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

Source: http://www.artfulsoftware.com/infotree/qrytip.php?id=460

Alternate Solution:
Ans 1. 

SET @i=0;
SELECT NAME, votes, @i:=@i+1 AS rank 
FROM votes 
ORDER BY votes DESC;
SET @i=0;

Share on Google Plus

About Admin

Arun is a JAVA/J2EE developer and passionate about coding and managing technical team.