Microsoft SQL Server Ranking Functions
Syntax
- DENSE_RANK ( ) OVER ( [ <partition_by_clause> ] < order_by_clause > )
- RANK ( ) OVER ( [ partition_by_clause ] order_by_clause )
Parameters
| Arguments | Details |
|---|---|
<partition_by_clause> | Divides the result set produced by the FROM clause into partitions to which the DENSE_RANK function is applied. For the PARTITION BY syntax, see OVER Clause (Transact-SQL). |
<order_by_clause> | Determines the order in which the DENSE_RANK function is applied to the rows in a partition. |
OVER ( [ partition_by_clause ] order_by_clause) | partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group. order_by_clause determines the order of the data before the function is applied. The order_by_clause is required. The <rows or range clause> of the OVER clause cannot be specified for the RANK function. For more information, see OVER Clause (Transact-SQL). |
Remarks
If two or more rows tie for a rank in the same partition, each tied rows receives the same rank. For example, if the two top salespeople have the same SalesYTD value, they are both ranked one. The salesperson with the next highest SalesYTD is ranked number two. This is one more than the number of distinct rows that come before this row. Therefore, the numbers returned by the DENSE_RANK function do not have gaps and always have consecutive ranks.
The sort order used for the whole query determines the order in which the rows appear in a result. This implies that a row ranked number one does not have to be the first row in the partition.
DENSE_RANK is nondeterministic. For more information, see Deterministic and Nondeterministic Functions.
