### Stats

63 Sunday, September 11, 2016
Not affiliated with Stack Overflow
Rip Tutorial: riptutorial@gmail.com

# Ranking Functions

## Syntax

• DENSE_RANK ( ) OVER ( [ <partition_by_clause> ] < order_by_clause > )
• RANK ( ) OVER ( [ partition_by_clause ] order_by_clause )

## Parameters

ArgumentsDetails
`<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.