SQL Commands


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

 

BookMark This Page




EnglishFrenchGermanItalianPortugueseRussianSpanish