Rank function in MySQL

Question

I need to find out rank of customers. Here I am adding the corresponding ANSI standard SQL query for my requirement. Please help me to convert it to MySQL .

SELECT RANK() OVER (PARTITION BY Gender ORDER BY Age) AS [Partition by Gender], 
  FirstName, 
  Age,
  Gender 
FROM Person

Is there any function to find out rank in MySQL?

Solution 1

One option is to use a ranking variable, such as the following:

SELECT    first_name,
          age,
          gender,
          @curRank := @curRank + 1 AS rank
FROM      person p, (SELECT @curRank := 0) r
ORDER BY  age;

The (SELECT @curRank := 0) part allows the variable initialization without requiring a separate SET command.

Test case:

CREATE TABLE person (id int, first_name varchar(20), age int, gender char(1));

INSERT INTO person VALUES (1, 'Bob', 25, 'M');
INSERT INTO person VALUES (2, 'Jane', 20, 'F');
INSERT INTO person VALUES (3, 'Jack', 30, 'M');
INSERT INTO person VALUES (4, 'Bill', 32, 'M');
INSERT INTO person VALUES (5, 'Nick', 22, 'M');
INSERT INTO person VALUES (6, 'Kathy', 18, 'F');
INSERT INTO person VALUES (7, 'Steve', 36, 'M');
INSERT INTO person VALUES (8, 'Anne', 25, 'F');

Result:

+------------+------+--------+------+
| first_name | age  | gender | rank |
+------------+------+--------+------+
| Kathy      |   18 | F      |    1 |
| Jane       |   20 | F      |    2 |
| Nick       |   22 | M      |    3 |
| Bob        |   25 | M      |    4 |
| Anne       |   25 | F      |    5 |
| Jack       |   30 | M      |    6 |
| Bill       |   32 | M      |    7 |
| Steve      |   36 | M      |    8 |
+------------+------+--------+------+
8 rows in set (0.02 sec)

Solution 2

Here is a generic solution that assigns dense rank over partition to rows. It uses user variables:

CREATE TABLE person (
    id INT NOT NULL PRIMARY KEY,
    firstname VARCHAR(10),
    gender VARCHAR(1),
    age INT
);

INSERT INTO person (id, firstname, gender, age) VALUES
(1,  'Adams',  'M', 33),
(2,  'Matt',   'M', 31),
(3,  'Grace',  'F', 25),
(4,  'Harry',  'M', 20),
(5,  'Scott',  'M', 30),
(6,  'Sarah',  'F', 30),
(7,  'Tony',   'M', 30),
(8,  'Lucy',   'F', 27),
(9,  'Zoe',    'F', 30),
(10, 'Megan',  'F', 26),
(11, 'Emily',  'F', 20),
(12, 'Peter',  'M', 20),
(13, 'John',   'M', 21),
(14, 'Kate',   'F', 35),
(15, 'James',  'M', 32),
(16, 'Cole',   'M', 25),
(17, 'Dennis', 'M', 27),
(18, 'Smith',  'M', 35),
(19, 'Zack',   'M', 35),
(20, 'Jill',   'F', 25);

SELECT person.*, @rank := CASE
    WHEN @partval = gender AND @rankval = age THEN @rank
    WHEN @partval = gender AND (@rankval := age) IS NOT NULL THEN @rank + 1
    WHEN (@partval := gender) IS NOT NULL AND (@rankval := age) IS NOT NULL THEN 1
END AS rnk
FROM person, (SELECT @rank := NULL, @partval := NULL, @rankval := NULL) AS x
ORDER BY gender, age;

Notice that the variable assignments are placed inside the CASE expression. This (in theory) takes care of order of evaluation issue. The IS NOT NULL is added to handle datatype conversion and short circuiting issues.

PS: It can easily be converted to row number over partition by by removing all conditions that check for tie.

| id | firstname | gender | age | rank |
|----|-----------|--------|-----|------|
| 11 | Emily     | F      | 20  | 1    |
| 20 | Jill      | F      | 25  | 2    |
| 3  | Grace     | F      | 25  | 2    |
| 10 | Megan     | F      | 26  | 3    |
| 8  | Lucy      | F      | 27  | 4    |
| 6  | Sarah     | F      | 30  | 5    |
| 9  | Zoe       | F      | 30  | 5    |
| 14 | Kate      | F      | 35  | 6    |
| 4  | Harry     | M      | 20  | 1    |
| 12 | Peter     | M      | 20  | 1    |
| 13 | John      | M      | 21  | 2    |
| 16 | Cole      | M      | 25  | 3    |
| 17 | Dennis    | M      | 27  | 4    |
| 7  | Tony      | M      | 30  | 5    |
| 5  | Scott     | M      | 30  | 5    |
| 2  | Matt      | M      | 31  | 6    |
| 15 | James     | M      | 32  | 7    |
| 1  | Adams     | M      | 33  | 8    |
| 18 | Smith     | M      | 35  | 9    |
| 19 | Zack      | M      | 35  | 9    |

Demo on db<>fiddle

Solution 3

While the most upvoted answer ranks, it doesn't partition, You can do a self Join to get the whole thing partitioned also:

SELECT    a.first_name,
      a.age,
      a.gender,
        count(b.age)+1 as rank
FROM  person a left join person b on a.age>b.age and a.gender=b.gender 
group by  a.first_name,
      a.age,
      a.gender

Use Case

CREATE TABLE person (id int, first_name varchar(20), age int, gender char(1));

INSERT INTO person VALUES (1, 'Bob', 25, 'M');
INSERT INTO person VALUES (2, 'Jane', 20, 'F');
INSERT INTO person VALUES (3, 'Jack', 30, 'M');
INSERT INTO person VALUES (4, 'Bill', 32, 'M');
INSERT INTO person VALUES (5, 'Nick', 22, 'M');
INSERT INTO person VALUES (6, 'Kathy', 18, 'F');
INSERT INTO person VALUES (7, 'Steve', 36, 'M');
INSERT INTO person VALUES (8, 'Anne', 25, 'F');

Answer:

Bill    32  M   4
Bob     25  M   2
Jack    30  M   3
Nick    22  M   1
Steve   36  M   5
Anne    25  F   3
Jane    20  F   2
Kathy   18  F   1

Solution 4

A tweak of Daniel's version to calculate percentile along with rank. Also two people with same marks will get the same rank.

set @totalStudents = 0;
select count(*) into @totalStudents from marksheets;
SELECT id, score, @curRank := IF(@prevVal=score, @curRank, @studentNumber) AS rank, 
@percentile := IF(@prevVal=score, @percentile, (@totalStudents - @studentNumber + 1)/(@totalStudents)*100),
@studentNumber := @studentNumber + 1 as studentNumber, 
@prevVal:=score
FROM marksheets, (
SELECT @curRank :=0, @prevVal:=null, @studentNumber:=1, @percentile:=100
) r
ORDER BY score DESC

Results of the query for a sample data -

+----+-------+------+---------------+---------------+-----------------+
| id | score | rank | percentile    | studentNumber | @prevVal:=score |
+----+-------+------+---------------+---------------+-----------------+
| 10 |    98 |    1 | 100.000000000 |             2 |              98 |
|  5 |    95 |    2 |  90.000000000 |             3 |              95 |
|  6 |    91 |    3 |  80.000000000 |             4 |              91 |
|  2 |    91 |    3 |  80.000000000 |             5 |              91 |
|  8 |    90 |    5 |  60.000000000 |             6 |              90 |
|  1 |    90 |    5 |  60.000000000 |             7 |              90 |
|  9 |    84 |    7 |  40.000000000 |             8 |              84 |
|  3 |    83 |    8 |  30.000000000 |             9 |              83 |
|  4 |    72 |    9 |  20.000000000 |            10 |              72 |
|  7 |    60 |   10 |  10.000000000 |            11 |              60 |
+----+-------+------+---------------+---------------+-----------------+

Solution 5

Combination of Daniel's and Salman's answer. However the rank will not give as continues sequence with ties exists . Instead it skips the rank to next. So maximum always reach row count.

    SELECT    first_name,
              age,
              gender,
              IF(age=@_last_age,@curRank:=@curRank,@curRank:=@_sequence) AS rank,
              @_sequence:=@_sequence+1,@_last_age:=age
    FROM      person p, (SELECT @curRank := 1, @_sequence:=1, @_last_age:=0) r
    ORDER BY  age;

Schema and Test Case:

CREATE TABLE person (id int, first_name varchar(20), age int, gender char(1));

INSERT INTO person VALUES (1, 'Bob', 25, 'M');
INSERT INTO person VALUES (2, 'Jane', 20, 'F');
INSERT INTO person VALUES (3, 'Jack', 30, 'M');
INSERT INTO person VALUES (4, 'Bill', 32, 'M');
INSERT INTO person VALUES (5, 'Nick', 22, 'M');
INSERT INTO person VALUES (6, 'Kathy', 18, 'F');
INSERT INTO person VALUES (7, 'Steve', 36, 'M');
INSERT INTO person VALUES (8, 'Anne', 25, 'F');
INSERT INTO person VALUES (9, 'Kamal', 25, 'M');
INSERT INTO person VALUES (10, 'Saman', 32, 'M');

Output:

+------------+------+--------+------+--------------------------+-----------------+
| first_name | age  | gender | rank | @_sequence:=@_sequence+1 | @_last_age:=age |
+------------+------+--------+------+--------------------------+-----------------+
| Kathy      |   18 | F      |    1 |                        2 |              18 |
| Jane       |   20 | F      |    2 |                        3 |              20 |
| Nick       |   22 | M      |    3 |                        4 |              22 |
| Kamal      |   25 | M      |    4 |                        5 |              25 |
| Anne       |   25 | F      |    4 |                        6 |              25 |
| Bob        |   25 | M      |    4 |                        7 |              25 |
| Jack       |   30 | M      |    7 |                        8 |              30 |
| Bill       |   32 | M      |    8 |                        9 |              32 |
| Saman      |   32 | M      |    8 |                       10 |              32 |
| Steve      |   36 | M      |   10 |                       11 |              36 |
+------------+------+--------+------+--------------------------+-----------------+

View more solutions

Comments

[Charles]

+1 for the devious inline initialization, that's a beautiful trick.

[Jesse Dhillon]

Didn't he ask for a partition though? My understanding of partitions is that the result set would have separate rankings for male and female.

[Daniel Vassallo]

@Jesse: If that is the case, I recently answered a similar question: stackoverflow.com/questions/3162389/multiple-ranks-in-one-ta‌​ble

[Fahim Parkar]

What if I want to give rank as 4 to Anne and Bob both?

[Daniel Vassallo]

@FahimParkar: See the following answer: stackoverflow.com/questions/2727138/… - Go down to the part that says "UPDATE:" You probably don't want to have an UPDATE statement, but you can probably get the logic from the inner SELECT.

[Gaspa79]

Even though this isn't really optimal in performance, it's awesome!

[a_horse_with_no_name]

This does not implement the example from the question as it misses the partition by gender part of the analytical function (which "numbers" the rank value per gender not for the overall result)

[kritya]

i know this is too old. But this doesnt/too slow works on very large tables

[modulitos]

@DanielVassallo What about ties? If there were ties, this would give them different ranks. Thus, I think this is a row numbering function, not a ranking function. Correct me if I am missing something.

[modulitos]

This solution, or Mukesh's solution, should be the correct solution. Although technically I believe both of you guys' solutions represent a dense ranking and not a regular rank. Here is a good explanation of the differences: sqlservercurry.com/2009/04/….

[logan]

@DanielVassallo: its not categorizing rank based on Gender !!

[Kim Stacks]

this is a wonderful answer precisely because I need to do a partition ranking. Thank you, sir!

[creator]

Can you also let us know how is .php code exactly should be? I tried to follow, but above code does not work. How to input to .php format?

[mike]

This solution is not very generic; it won't work if rank_column has a value of 0. sqlfiddle.com/#!2/9c5dd/1

[ForeverLearner]

As I see, for duplicate values, we are assigning different ranks. That's not the correct behavior of Rank function.

[xmedeko]

It may become much slower than above solutions when the number of rows in the Person table grows. It's O(n^2) vs O(n) slower.

[xmedeko]

IMO it has same complexity as subselect in the @Sam Kidman's answer: O(n^2). But dunno know if it's possible to do it better in MySQL.

[xmedeko]

@mike Use @rank_count := IF(@prev_value = rank_column, @rank_count, @rank_count + 1) instead.

[narduk]

I am new to MySQL but is this solution ok? In MySQL docs says "the order of evaluation for expressions involving user variables is undefined." dev.mysql.com/doc/refman/5.7/en/user-variables.html

[Andy]

This is not not giving the rank, it is giving the row number - the rank should be the same if the values are the same

[Prince Odame]

@mike Add an ELSE section to the CASE statement: ELSE @rank_count := @rank_count + 1

[ferics2]

Check out onlamp.com/pub/a/mysql/2007/03/29/… for a great tutorial along the same lines

[philipxy]

None of this is justfied by the documentation. It is just (fuzzy) speculation. As are all the answers both using & writing the same variable, which the manual says is explicitly not defined, although the manual does have a lot of unhelpful text re what might work as you expect, without saying what it thinks you expect or what use a description of non-guaranteed behaviour is. PS As of 8.0 variable assignment outside SET is deprecated.

[newdark-it]

It is not wrong just does not work with older versions of SQL. plus it was kinda of copy and past of his question so it does not feel like it fits the answer.

[Timo]

Self-join to get the rank! That's great. At last, a solution without variables and without MySQL 8 window functions. :)

[Salman A]

@abhash ORDER BY gender, age DESC?

[Steve Smith]

@brand-it For those of on MySQL 8+, this answer is important since it lets us know that Rank is now available. If I hadn't scrolled down this far, I'd assume the earlier answers were the only solution.

[newdark-it]

@SteveSmith Good point it is nice to have this answer for those use the newer version of MYSQL.

[James Bond]

Yes, I am discouraged by a lot of answers with the user variables and logic blocks. A new versions of MySQL allows do it MUCH simple with RANK() function that offers a built in grouping by partitions.

[The Impaler]

This answer is wrong. It doesn't address the PARTITION BY. Shouldn't be upvoted or accepted.

[Widada]

I run this query in 50k records, I got the timeout