Calculation of Rank in MySQL Query

Calculation of Rank in MySQL QueryetShare

In this post, I am sharing a demonstration on how to calculate the Rank in MySQL query.

The RANK() available in Microsoft SQL Server and MySQL doesn’t have any default RANK().

You can calculate RANK using an inline query variable for checking the previous value.

If previous record matches to the current record, increment your rank variable.
The ORDER BY clause is mandatory for that column on which you are going to calculate RANK.

Below is a full example:

Let’s First creates sample table and data.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

CREATE DATABASE Employee;   CREATE TABLE Employee.tbl_EmpSalary (     EmpID INT     ,EmpSalary INT ); INSERT INTO Employee.tbl_EmpSalary VALUES (1, 10000) ,(2, 20000) ,(3, 26000) ,(4, 10000) ,(5, 35000) ,(6, 40000) ,(7, 42000) ,(8, 20000);

Now let’s calculate RANK on employee salary.

1 2 3 4 5 6 7 8 9 10 11 12 13

SET @PreviousRecord = NULL; SET @Rank = 0; SELECT     EmpID     ,EmpSalary     ,CASE WHEN @PreviousRecord = EmpSalary             THEN @Rank WHEN @PreviousRecord := EmpSalary             THEN @Rank := @Rank + 1      END AS EmpSalaryRank FROM Employee.tbl_EmpSalary ORDER BY EmpSalary;

Result: