MSSQL DENSE RANK Tutorial
The DENSE_RANK function is used to rank the repeating values in a manner such that similar values are ranked the same without any gaps between the rankings. In other words, dense_rank function returns the rank of each row in continuous series within the partition of a result set. The rank of a row is one plus the number of distinct ranks that come before the row in question.
Note: This is same as the RANK() function. Only difference is returns rank without gaps.
MSSQL DENSE_RANK FUNCTION SYNTAX
DENSE_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 DENSE_RANK () function.
MSSQL DENSE_RANK FUNCTION
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 |
DENSE_RANK Function Example
DENSE_RANK function without using partition_by_clause from "Employee” table:
SELECT EmployeeID, EmployeeName, Gender, Location,
DENSE_RANK() OVER (order by Gender DESC)AS DenseRank
FROM Employee;
DENSE_RANK Function Results
DENSE_RANK Function without using partition_by_clause output
| Employee ID | Employee Name | Gender | Location | Salary |
| 1001 | Henry | Male | New York | 1 |
| 1003 | John | Male | London | 1 |
| 1004 | Mile | Male | London | 1 |
| 1002 | Tina | Female | Moscow | 2 |
| 1005 | Tara | Female | Moscow | 2 |
| 1006 | Sophie | Female | London | 2 |
DENSE_RANK Function Example
With partition_by_clause from “Employee” table
SELECT EmployeeID, EmployeeName, Gender, Location,
RANK() OVER (partition by Location, order by Gender DESC)AS Rank
FROM Employee;
DENSE_RANK Function Results
With partition_by_clause produces the following output.
| Employee ID | Employee Name | Gender | Location | Salary |
| 1003 | John | Male | London | 1 |
| 1004 | Mile | Male | London | 1 |
| 1006 | Sophie | Female | London | 2 |
| 1002 | Tina | Female | Moscow | 1 |
| 1005 | Tara | Female | Moscow | 1 |
| 1001 | Henry | Male | New York | 1 |
