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. Return type of DENSE_RANK() is bigint. Syntax for DENSE_RANK() is
</pre> <pre><code>DENSE_RANK ( ) OVER ( [ <> ] <> )] <> ) </code></pre> <pre>
<> Divides the result set produced by the FROM clause into partitions to which the DENSE_RANK function is applied. <> 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 that is used in a ranking function. The following example of DENSE_RANK() from BOL 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