RANK(), DENSE_RANK() and NTILE() are newly added functions in SQL Server 2005 T-SQL
1) ROW_NUMBER () OVER ([partition_by_clause] order_by_clause )
2) RANK () OVER ([partition_by_clause] order_by_clause)
RANK() returns the rank of each row within the partition of a result set. When there is a tie,the same rank is assigned to the tied rows.
For example, 1, 2, 3, 3, 3, 6, 7, 7, 9, 10
3) DENSE_RANK () OVER ([partition_by_clause] order_by_clause )
DENSE_RANK() works like RANK(), except that the numbers being returned are packed (do not have gaps) and always have consecutive ranks.
For example, 1, 2, 3, 3, 3, 4, 5, 5, 6, 7
4) NTILE (integer_expression) OVER ([partition_by_clause] order_by_clause)
EXAMPLE:
1) ROW_NUMBER () OVER ([partition_by_clause] order_by_clause )
2) RANK () OVER ([partition_by_clause] order_by_clause)
RANK() returns the rank of each row within the partition of a result set. When there is a tie,the same rank is assigned to the tied rows.
For example, 1, 2, 3, 3, 3, 6, 7, 7, 9, 10
3) DENSE_RANK () OVER ([partition_by_clause] order_by_clause )
DENSE_RANK() works like RANK(), except that the numbers being returned are packed (do not have gaps) and always have consecutive ranks.
For example, 1, 2, 3, 3, 3, 4, 5, 5, 6, 7
4) NTILE (integer_expression) OVER ([partition_by_clause] order_by_clause)
EXAMPLE:
