# DENSE_RANK (Transact-SQL)

Returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question.

Transact-SQL Syntax Conventions

## Syntax

```
DENSE_RANK ( ) OVER ( [ < partition_by_clause > ] < order_by_clause > )
```

## Arguments

- < partition_by_clause >

Divides the result set produced by the FROM clause into partitions to which the DENSE_RANK function is applied. For the syntax of PARTITION BY, see OVER Clause (Transact-SQL).

- < order_by_clause >

Determines the order in which the DENSE_RANK values are applied to the rows in a partition. An integer cannot represent a column in the <order_by_clause> that is used in a ranking function.

## Return Types

**bigint**

## 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.

## Examples

The following example returns the `DENSE_RANK`

of the quantity of products at the various locations. Notice that the `ORDER BY`

in the `OVER`

clause orders the `DENSE_RANK`

and the `ORDER BY`

of the `SELECT`

statement orders the result set.

```
USE AdventureWorks;
GO
SELECT i.ProductID, p.Name, i.LocationID, i.Quantity,
DENSE_RANK() OVER (PARTITION BY i.LocationID ORDER BY i.Quantity) AS DENSE_RANK
FROM Production.ProductInventory i
JOIN Production.Product p ON i.ProductID = p.ProductID
ORDER BY Name;
GO
```

## See Also

#### Reference

RANK (Transact-SQL)

ROW_NUMBER (Transact-SQL)

NTILE (Transact-SQL)

Ranking Functions (Transact-SQL)

Functions (Transact-SQL)