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:

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:

This query combines multiple groupings through the use of GROUPING SETS. Hence, 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].