skip to main content
Functions : Scalar functions : DENSE_RANK
 

DENSE_RANK

Syntax 

DENSE_RANK() OVER([PARTITION BY value_expr] ORDER BY value_expr)

Description

This function returns the rank of all rows without gaps in the sequential numbering of the ranking. If a partition is specified, it divides the result set into partitions. The rank value is 1 + the number of ranks that occur before the specified row. If two or more rows qualify for the same rank in the same partition, each of those rows receives the same rank. The sort order of the SELECT statement determines the order in which the rows appear in the result, so a row with a rank of 1 may not be the first row in the partition.
The OVER clause determines the partitioning and ordering of the rows in the result before the DENSE_RANK function is applied using the following clauses:
• The PARTITION BY clause divides the result set produced by the FROM clause into partitions to which the DENSE_RANK function is applied. This clause is optional.
• The ORDER BY clause determines the order in which DENSE_RANK values are applied to the rows. An integer cannot represent a column in the ORDER BY clause.
value_expr must refer to columns made available by the FROM clause in the SELECT statement. It can be any of the following values:
• Column in the select list
• Scalar subquery
• Scalar function
• User-defined variable
It cannot refer to expressions or aliases.

Example 

SELECT DENSE_RANK() OVER(ORDER BY Age) AS [Dense Rank By Age],
FirstName, Age FROM Person
This statement returns the following results:
 
Dense Rank By Age
FirstName
Age
1
2
2
3
3
4
5
5
6
7
8
Larry
Doris
George
Mary
Sherry
Sam
Ted
Marty
Sue
Frank
John
5
6
6
11
11
17
23
23
29
38
40
In this result, the Dense Rank By Age values are sequential without any gaps, and when two rows have the same ORDER BY value, they have the same ranking (for example, Doris and George).