DENSE_RANK function

Similar to the RANK function, you use the DENSE_RANK function to return the rank of the value in the current row as compared to the value in other rows. The rank of a value reflects the order in which it would appear if the list of values were sorted. Rank is calculated for the expression specified in the window's ORDER BY clause.

The DENSE_RANK function returns a series of ranks that are monotonically increasing with no gaps, or jumps in rank value. The term dense is used because there are no jumps in rank value (unlike the RANK function).

As the window moves down the input rows, the rank is calculated for the expression specified in the window's ORDER BY clause. If the ORDER BY clause includes multiple expressions, the second and subsequent expressions are used to break ties if the first expression has the same value in adjacent rows. NULL values are sorted before any other value (in ascending sequence).

Example 1

The following query determines the three most expensive products in the database. A descending sort sequence is specified for the window so that the most expensive products have the lowest rank (rankings start at 1).

SELECT Top 3 *
  FROM ( SELECT Description, Quantity, UnitPrice,
    DENSE_RANK( ) OVER ( ORDER BY UnitPrice DESC ) AS Rank            
    FROM Products ) AS DT
  ORDER BY Rank;

This query returns the following result:

Description Quantity UnitPrice Rank
1 Hooded Sweatshirt 39 24.00 1
2 Zipped Sweatshirt 32 24.00 1
3 Cotton Shorts 80 15.00 2

Note that rows 1 and 2 have the same value for Unit Price, and therefore also have the same rank. This is called a tie.

With the DENSE_RANK function, there is no jump in the rank value after a tie. For example, the rank value for row 3 is 2. This is different from the RANK function, where a jump in rank values occurs after a tie. See RANK function.

Example 2

Because windows are evaluated after a query's GROUP BY clause, you can specify complex requests that determine rankings based on the value of an aggregate function.

The following query produces the top three salespeople in each region by their total sales within that region, along with the total sales for each region:

SELECT * 
  FROM ( SELECT o.SalesRepresentative, o.Region,
             SUM( s.Quantity * p.UnitPrice ) AS total_sales,
             DENSE_RANK( ) OVER ( PARTITION BY o.Region, 
               GROUPING( o.SalesRepresentative )
               ORDER BY total_sales DESC ) AS sales_rank
           FROM Products p, SalesOrderItems s, SalesOrders o
           WHERE p.ID = s.ProductID AND s.ID = o.ID
           GROUP BY GROUPING SETS( ( o.SalesRepresentative, o.Region ), 
              o.Region ) ) AS DT
  WHERE sales_rank <= 3 
  ORDER BY Region, sales_rank;

This query returns the following result:

SalesRepresentative Region total_sales sales_rank
1 299 Canada 9312.00 1
2 (NULL) Canada 24768.00 1
3 1596 Canada 3564.00 2
4 856 Canada 2724.00 3
5 299 Central 32592.00 1
6 (NULL) Central 134568.00 1
7 856 Central 14652.00 2
8 467 Central 14352.00 3
9 299 Eastern 21678.00 1
10 (NULL) Eastern 142038.00 1
11 902 Eastern 15096.00 2
12 690 Eastern 14808.00 3
13 1142 South 6912.00 1
14 (NULL) South 45262.00 1
15 667 South 6480.00 2
16 949 South 5782.00 3
17 299 Western 5640.00 1
18 (NULL) Western 37632.00 1
19 1596 Western 5076.00 2
20 667 Western 4068.00 3

This query combines multiple groupings through the use of GROUPING SETS. So, the WINDOW PARTITION clause for the window uses the GROUPING function to distinguish between detail rows that represent particular salespeople and the subtotal rows that list the total sales for an entire region. The subtotal rows by region, which have the value NULL for the sales rep attribute, each have the ranking value of 1 because the result's ranking order is restarted with each partition of the input; this ensures that the detail rows are ranked correctly starting at 1.

Finally, note in this example that the DENSE_RANK function ranks the input over the aggregation of the total sales. An aliased select list item is used as a shorthand in the WINDOW ORDER clause.

For more information about the syntax for the DENSE_RANK function, see DENSE_RANK function [Ranking].