Collapse the table of content
Expand the table of content


SQL Server 2000


Returns the one-based rank of a specified tuple in a specified set.


Rank(«Tuple», «Set»[, «Calc Expression»])


If «Calc Expression» is not specified, the Rank function returns the one-based ordinal position of a tuple, specified in «Tuple», within a set specified in «Set».

If «Calc Expression» is specified, the Rank function evaluates the numeric expression specified in «Calc Expression» against the tuple to determine its one-based rank. When «Calc Expression» is specified, the Rank function assigns the same rank to tuples in a set with duplicate values. However, the presence of duplicate values affects the ranks of subsequent tuples in the set. For example, if the tuple (a,b) had the same value as the tuple (c,d) in the set {(a,b), (e,f), (c,d)}, and the tuple (a,b) has a rank of 1, then (a,b) and (c,d) would both have a rank of 1, but (e,f) would have a rank of 3. No tuple would have a rank of 2 in the set.

The Rank function does not order the set.


The following example returns 3:

Rank((c,d), {(a,b), (e,f), (c,d)})

However, if the tuples in the set { (a,b), (e,f), (c,d) } have values of 1, 8, and 3, respectively, in the [Test] measure, the following example returns 2:

Rank ((c,d), {(a,b), (e,f), (c,d)}, Measures.Test)
© 2015 Microsoft