Following are the Ranking functions in SQL Server 2008 R2;
the rank of each row within the partition of a result set. The rank of a row is
one plus the number of ranks that come before the row in question
two or more rows tie for a rank, 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
three, because there are two rows that are ranked higher. Therefore, the RANK
function does not always return consecutive integers.
sort order that is used for the whole query determines the order in which the
rows appear in a result set.
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.
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.
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.
the rows in an ordered partition into a specified number of groups. The groups
are numbered, starting
at one. For each row, NTILE returns the number of the
group to which the row belongs.
the number of rows in a partition is not divisible by integer_expression, this
will cause groups of two sizes that differ by one member. Larger groups come
before smaller groups in the order specified by the OVER clause. For example if
the total number of rows is 53 and the number of groups is five, the first
three groups will have 11 rows and the two remaining groups will have 10 rows
each. If on the other hand the total number of rows is divisible by the number
of groups, the rows will be evenly distributed among the groups. For example, if
the total number of rows is 50, and there are five groups, each bucket will
contain 10 rows.
the sequential number of a row within a partition of a result set, starting at
1 for the first row in each partition.
ORDER BY clause determines the sequence in which the rows are assigned their
unique ROW_NUMBER within a specified partition.