The RANK() functions ranks each row of a result set. It can also be used to partition the data for ranking. It takes two arguments, PARTITION BY clause and ORDER BY clause. As the name suggests PARTITION BY clause is used to partition the result set into chunks, and ORDER BY defines the order of the rows.

For example,

-- © 2011 – Vishal (http://SqlAndMe.com)
 
USE   AdventureWorks2008R2
 
SELECT      TOP (10)
            OrganizationLevel, JobTitle, LoginID, HireDate,
            RANK() OVER (ORDER BY HireDate ASC) AS 'Rank'
FROM        HumanResources.Employee

Result Set:

In the above example, RANK() function is used to rank each row based on HireDate, no partitioning is used. Now if we need to rank all rows for each OrganizationLevel individually, we need to add PARTITION BY clause to RANK() as below:

SELECT      TOP (10)
            OrganizationLevel, JobTitle, LoginID, HireDate,
            RANK() OVER (PARTITION BY OrganizationLevel
                        ORDER BY HireDate ASC) AS 'Rank'
FROM  HumanResources.Employee

Result Set:

In this case, for OrganizationLevel = 1, the ranking again starts from 1, same way for OrganizationLevel = 2.

However, RANK() functions does not always assign consecutive numbers to rows, if you look at the first example, there is a tie while assigning rank 8, in this case both rows are assigned rank 8, and the next rank assigned is 10.

In cases where you need consecutive numbers to be assigned, you can use DENSE_RANK():

SELECT      TOP (10)
            OrganizationLevel, JobTitle, LoginID, HireDate,
            DENSE_RANK() OVER (ORDER BY HireDate ASC) AS 'Rank'
FROM  HumanResources.Employee

Result Set:

Hope This Helps! Cheers!