Journey to SQL Authority with Pinal Dave

I have not written about this subject for long time, as I strongly believe that Book On Line explains this concept very well. SQL Server 2005 has total of 4 ranking function. Ranking functions return a ranking value for each row in a partition. All the ranking functions are non-deterministic.

ROW_NUMBER () OVER ([<partition_by_clause>] <order_by_clause>)
Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

RANK () OVER ([<partition_by_clause>] <order_by_clause>)
Returns the rank of each row within the partition of a result set.

DENSE_RANK () OVER ([<partition_by_clause>] <order_by_clause>)
Returns the rank of rows within the partition of a result set, without any gaps in the ranking.

NTILE (integer_expression) OVER ([<partition_by_clause>] <order_by_clause>)
Distributes the rows in an ordered partition into a specified number of groups.

All the above definition and syntax are taken from BOL. It is difficult to explain above function anything else than what they are explained in BOL. Following example is excellent example from BOL again. This function explains usage of all the four function together in one query.

USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
            ,ROW_NUMBER() OVER (
        ORDER BY a.PostalCode) AS 'Row Number'
            ,RANK() OVER (
        ORDER BY a.PostalCode) AS 'Rank'
            ,DENSE_RANK() OVER (
        ORDER BY a.PostalCode) AS 'Dense Rank'
            ,NTILE(4) OVER (
        ORDER BY a.PostalCode) AS 'Quartile'
            ,s.SalesYTD, a.PostalCode
    FROM Sales.SalesPerson s
            INNER JOIN Person.Contact c
            ON s.SalesPersonID = c.ContactID
            INNER JOIN Person.Address a
            ON a.AddressID = c.ContactID
    WHERE TerritoryID IS NOT NULL
        AND SalesYTD <> 0;

Resultset:

Most of the content of this article is taken from BOL.

Reference : Pinal Dave (http://www.SQLAuthority.com), ROW_NUMBER, RANK, DENSE_RANK, NTILE