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.

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:

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:


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:


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.

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.

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.

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.

You Might Interested In