Execute the following Microsoft T-SQL example query scripts in SQL Server Management Studio Query Editor to compare the ROW_NUMBER, RANK, DENSE_RANK and NTILE ranking functions.

-- SQL RANK function - Quick Syntax - sql rank over partition

SELECT

      CustomerID,

      convert(date,OrderDate) as OrderDate,

      RANK() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC) AS RankNo

FROM AdventureWorks2008.Sales.SalesOrderHeader

ORDER BY CustomerID, RankNo

/* Partial results

CustomerID  OrderDate   RankNo

11035       2004-05-18  1

11035       2004-03-13  2

11035       2001-08-09  3

11036       2003-07-26  1

11037       2003-07-24  1

*/

------------

-- SQL RANK function usage - select most recent order(s) for each customer

SELECT * FROM

(SELECT

      CustomerID,

      convert(date,OrderDate) as OrderDate,

      RANK() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC) AS RankNo

FROM AdventureWorks2008.Sales.SalesOrderHeader ) x

WHERE RankNo = 1

ORDER BY CustomerID

------------

------------

-- Microsoft T-SQL ranking functions comparison

------------

-- MSSQL row_number (row number sequence) - NTILE

-- MSSQL rank and dense_rank functions - over order by clause

-- Data is rounded down to the thousands for ranking

USE AdventureWorks;

SELECT  

         c.AccountNumber                  AS CustAccount,

         FLOOR(h.SubTotal /1000)          AS [SubTotal (Thousands $)],

         ROW_NUMBER()

           OVER(ORDER BY FLOOR(h.SubTotal /1000) DESC) AS RowNumber,

         RANK()

           OVER(ORDER BY FLOOR(h.SubTotal /1000) DESC) AS Rank,

         DENSE_RANK()

           OVER(ORDER BY FLOOR(h.SubTotal /1000) DESC) AS DenseRank,

         NTILE(5)

           OVER(ORDER BY FLOOR(h.SubTotal /1000) DESC) AS NTile

FROM     Sales.Customer c

         INNER JOIN Sales.SalesOrderHeader h

           ON c.CustomerID = h.CustomerID

         INNER JOIN Sales.SalesTerritory t

           ON h.TerritoryID = t.TerritoryID

WHERE    t.Name = 'Germany'

  AND    YEAR(OrderDate) = 2004

  AND    SubTotal >= 4000.0

ORDER BY RowNumber;

GO

/* Results

*/

------------