SQL Server: DENSE RANK Function

The DENSE_RANK function ranks rows based on the PARTITION BY and ORDER BY specified in the OVER clause. The basic syntax of the DENSE_RANK function is as follows:

DENSE_RANK ( )

OVER ([partition_by] order_by)

First, if a partitioned by value is specified, the rows are partitioned. Then, within each partition, the rows are sorted and ranked based on the specified ORDER BY clause. If two rows within the same partition have the same ORDER BY value, they are assigned the same ranking, and the following ranked row is assigned the next sequential ranking value. For example, the following SELECT statement could be used in this scenario:

SELECT *,

DENSE_RANK () OVER
(PARTITION BY Region ORDER BY Quota DESC)
AS Ranking
FROM Salesperson;

This statement would partition the salespeople by region. Then, within each region, the rows would be sorted in descending order based on each salesperson's Quota value. Finally, the Ranking value would be calculated for each salesperson. Salespeople with identical Quota values within a region would have the same ranking, and no ranking values would be skipped. DBA/Developer should not include a RANK function in the SELECT list and a correlated subquery in the FROM clause because a subquery is not needed in this scenario. DBA/Developer can use the RANK function to rank a result set. A correlated subquery is a subquery that references one or more columns in the outer query. Correlated sub queries can adversely affect performance, and should be avoided when possible because the inner query will execute once for each row of the outer query. While correlated sub queries are required in some situations, in this scenario DBA/Developer could accomplish the desired result using the aggregate DENSE_RANK function with an OVER clause. While the RANK function is similar to the DENSE_RANK function, it skips a ranking value for each row that has an identical ORDER BY value. DBA/Developer should not include an OUTER APPLY or a CROSS APPLY clause. The APPLY clause is used in the FROM clause of a query to join a table to a table-valued function. The table-valued function is called for each row returned by the outer query. The APPLY clause allows DBA/Developer to easily call a table-valued function for each row returned by a query. The OUTER APPLY clause returns all rows from the outer query, even if the row does not return a value for the tablevalued function. The CROSS APPLY clause returns only the outer query rows for which the table-valued function returned a value.