Query to obtain rank function in MySQL

There is no rank function in MySQL actually, whenever you need to query rank from table based on some functionality/criteria in MYSQL. But there are simple queries that can be used to perform the same.

Here I’m describing queries to perform rank with examples:

First of all we are creating `players` table where we want to get the rank. You will be familiar with many queries depending on various requirements.

CREATE TABLE `players` (
  `pid` int(2) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `age` int(2) NOT NULL,
  PRIMARY KEY (`pid`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

INSERT INTO `players` (`pid`, `name`, `age`) VALUES
(1, 'Samual', 25),
(2, 'Vino', 20),
(3, 'John', 20),
(4, 'Andy', 22),
(5, 'Brian', 21),
(6, 'Dew', 24),
(7, 'Kris', 25),
(8, 'William', 26),
(9, 'George', 23),
(10, 'Peter', 19),
(11, 'Tom', 20),
(12, 'Andre', 20);

Query to obtain rank function in MySQL

Here we wish to obtain a rank column with ascending order of player’s age. So our query will be:

SELECT pid, name, age, @curRank := @curRank + 1 AS rank
FROM players p, (
SELECT @curRank := 0
) q
ORDER BY age

| PID |    NAME | AGE | RANK |
|-----|---------|-----|------|
|  10 |   Peter |  19 |    1 |
|  12 |   Andre |  20 |    2 |
|   2 |    Vino |  20 |    3 |
|   3 |    John |  20 |    4 |
|  11 |     Tom |  20 |    5 |
|   5 |   Brian |  21 |    6 |
|   4 |    Andy |  22 |    7 |
|   9 |  George |  23 |    8 |
|   6 |     Dew |  24 |    9 |
|   7 |    Kris |  25 |   10 |
|   1 |  Samual |  25 |   11 |
|   8 | William |  26 |   12 |

To declare a variable in mysql you have to use ‘@’ sign before variable name.  The `(@curRank := 0)` part in FROM clause allows us the variable initialization without requiring a separate SET command. You can use `SET` as well but it will process two queries:

SET @curRank := 0;
SELECT pid, name, age, @curRank := @curRank + 1 AS rank
FROM players
ORDER BY age

Query to calculate rank in descending order

More ever to obtain rank in descending order of age then name, modify the query at `ORDER BY` clause with `DESC` and column name:

SELECT pid, name, age, @curRank := @curRank + 1 AS rank
FROM players p, (
SELECT @curRank := 0
) q
ORDER BY age DESC, name

| PID |    NAME | AGE | RANK |
|-----|---------|-----|------|
|   8 | William |  26 |    1 |
|   7 |    Kris |  25 |    2 |
|   1 |  Samual |  25 |    3 |
|   6 |     Dew |  24 |    4 |
|   9 |  George |  23 |    5 |
|   4 |    Andy |  22 |    6 |
|   5 |   Brian |  21 |    7 |
|  12 |   Andre |  20 |    8 |
|   3 |    John |  20 |    9 |
|  11 |     Tom |  20 |   10 |
|   2 |    Vino |  20 |   11 |
|  10 |   Peter |  19 |   12 |

Rank in MySQL when rows ties

Now what if  we wish to assign same rank to rows with ties means rows with same values for the rank comparison column (`age` in our case) should hold the same rank while calculating rank in MySQL. For this purpose we have used an extra variable.

SELECT pid, name, age, 
CASE 
WHEN @prevRank = age THEN @curRank 
WHEN @prevRank := age THEN @curRank := @curRank + 1
END AS rank
FROM players p, 
(SELECT @curRank :=0, @prevRank := NULL) r
ORDER BY age

| PID |    NAME | AGE | RANK |
|-----|---------|-----|------|
|  10 |   Peter |  19 |    1 |
|  12 |   Andre |  20 |    2 |
|   2 |    Vino |  20 |    2 |
|   3 |    John |  20 |    2 |
|  11 |     Tom |  20 |    2 |
|   5 |   Brian |  21 |    3 |
|   4 |    Andy |  22 |    4 |
|   9 |  George |  23 |    5 |
|   6 |     Dew |  24 |    6 |
|   7 |    Kris |  25 |    7 |
|   1 |  Samual |  25 |    7 |
|   8 | William |  26 |    8 |

As shown when two or more rows for a rank in the same partition, each tied rows receives the same rank. Players Andre, Vino, John and Tom have the same age so they are are ranked number 2. The player with next highest age (Brian) is ranked number 3.  This query is equivalent to DENSE_RANK() function in MSSQL and ORACLE.

RANK() equivalent in MySQL

while using the RANK() function, if two or more rows tie for a rank, each tied rows receives the same rank, however with gaps in the ranking where there are ties.

SELECT pid, name, age, rank FROM
(SELECT pid, name, age,
@curRank := IF(@prevRank = age, @curRank, @incRank) AS rank, 
@incRank := @incRank + 1, 
@prevRank := age
FROM players p, (
SELECT @curRank :=0, @prevRank := NULL, @incRank := 1
) r 
ORDER BY age) s

Here is a subquery in query. We are using three variables to calculate the rank in case of ties and to omit additional columns in query result (`@incRank`, `@prevRank`, `@curRank`) we have enclosed subquery into query. It’s the RANK() function available in MSSQL and ORACLE.

| PID |    NAME | AGE | RANK |
|-----|---------|-----|------|
|  10 |   Peter |  19 |    1 |
|  12 |   Andre |  20 |    2 |
|   2 |    Vino |  20 |    2 |
|   3 |    John |  20 |    2 |
|  11 |     Tom |  20 |    2 |
|   5 |   Brian |  21 |    6 |
|   4 |    Andy |  22 |    7 |
|   9 |  George |  23 |    8 |
|   6 |     Dew |  24 |    9 |
|   7 |    Kris |  25 |   10 |
|   1 |  Samual |  25 |   10 |
|   8 | William |  26 |   12 |

Here we can see that players Andre, Vino, John and Tom have the same age so they are are ranked number 2. The player with next highest age (Brian) is ranked number 6 instead of 3, because there are four rows that are ranked at position 2.

Well I hope after seeing these examples, you will understanding the difference between the RANK() and DENSE_RANK() as well as will know where to use which query to obtain rank function in MySQL.