This article is half-done without your Comment! *** Please share your thoughts via Comment ***
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:
