MSSQL RANK Tutorial
MSSQL RANK function is used to rank the repeating values in a manner such that similar values are ranked the same. In other words, rank function returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question.
MSSQL RANK Function SYNTAX
RANK ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> )
The partition_by_clause splits the rows generated into different sets. If partition_by_clause is not specified, then all rows shall be treated as a single group.
The priority of the ranking is determined by order_by_clause. The order_by_clause unlike partition_by_clause is essential in Rank function.
MSSQL RANK FUNCTION EXAMPLE
Example database table “Employee”
| Employee ID | Employee Name | Age | Gender | Location | Salary |
| 1001 | Henry | 54 | Male | New York |
100000 |
| 1002 | Tina | 36 | Female | Moscow | 80000 |
| 1003 | John | 24 | Male | London | 40000 |
| 1004 | Mile | 31 | Male | London | 70000 |
| 1005 | Tara | 26 | Female | Moscow | 50000 |
| 1006 | Sophie | 29 | Female | London | 60000 |
Example of using MSSQL RANK function without using partition_by_clause from “Employee” table:
SELECT EmployeeID, EmployeeName, Gender, Location,
RANK() OVER (order by Gender DESC)AS Rank
FROM Employee;
MSSQL RANK FUNCTION Results
This will give the following rank results.
| Employee ID | Employee Name | Age | Gender | Location | Salary |
| 1001 | Henry | 54 | Male | New York | 1 |
| 1003 | John | 24 | Male | London | 1 |
| 1004 | Mile | 31 | Male | London | 1 |
| 1002 | Tina | 36 | Female | Moscow | 4 |
| 1005 | Tara | 26 | Female | Moscow | 4 |
| 1006 | Sophie | 29 | Female | London | 4 |
MSSQL RANK FUNCTION EXAMPLE 2
MSSQL RANK Function with partition_by_clause from “Employee” table above.
SELECT EmployeeID, EmployeeName, Gender, Location,
RANK() OVER (partition by Location, order by Gender DESC)AS Rank
FROM Employee;
RANK Function Results Example 2
MSSQL RANK function using partition_by_clause will provide the following results.
| Employee ID | Employee Name | Gender | Location | Salary |
| 1003 | John | Male | London | 1 |
| 1004 | Mile | Male | London | 1 |
| 1006 | Sophie | Female | London | 3 |
| 1002 | Tina | Female | Moscow | 1 |
| 1005 | Tara | Female | Moscow | 1 |
| 1001 | Henry | Male | New York | 1 |
