SQL SERVER 2005 Ranking Functions (Transact-SQL) – ROW_NUMBER, RANK, DENSE_RANK, NTILE

SQL Server 2005 ranking functions

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()

RANK()

DENSE_RANK()

NTILE()

ROW_NUMBER () OVER ([] )
Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.It Returns Bigint type Value

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

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

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

****************************************************************

CREATE TABLE OrderRanking (OrderID INT IDENTITY(1,1) NOT NULL, CustomerID INT, OrderTotal decimal(15,2))

************************Inserting Rows Into the table *****************************

INSERT OrderRanking (CustomerID, OrderTotal)
SELECT 1, 1000
UNION
SELECT 1, 500
UNION
SELECT 1, 650
UNION
SELECT 1, 3000
UNION
SELECT 2, 1000
UNION
SELECT 2, 2000
UNION
SELECT 2, 500
UNION
SELECT 2, 500
UNION
SELECT 3, 500
UNION
SELECT 4, 100

*******************Query for the Ranking Function One by One **********************

SELECT  *,

ROW_NUMBER() OVER (ORDER BY OrderTotal DESC) AS [Row No],    

   
ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderTotal DESC) AS [RowNo Partitionwise],

RANK() OVER (ORDER BY OrderTotal DESC) AS [Rank],

RANK() OVER (PARTITION BY CustomerID ORDER BY OrderTotal DESC) AS [Rank Partitionwise],

DENSE_RANK() OVER (ORDER BY OrderTotal DESC) AS [Dense Rank],

DENSE_RANK() OVER (PARTITION BY CustomerID ORDER BY OrderTotal DESC) AS [Dense Rank Partitionwise],

NTILE(2) OVER (ORDER BY OrderTotal) AS [NTILE]

FROM OrderRanking ORDER BY OrderTotal DESC

Query Results:

==================== For more information ===================

- NTILE { http://msdn2.microsoft.com/en-us/library/ms175126.aspx }
- RANK { http://msdn2.microsoft.com/en-us/library/ms176102.aspx }
- DENSE_RANK { http://msdn2.microsoft.com/en-us/library/ms173825.aspx }
- ROW_NUMBER { http://msdn2.microsoft.com/en-us/library/ms186734.aspx }
- Ranking functions { http://msdn2.microsoft.com/en-us/library/ms189798.aspx }
- OVER clause { http://msdn2.microsoft.com/en-us/library/ms189461.aspx }
- ORDER BY clause { http://msdn2.microsoft.com/en-us/library/ms188385.aspx }
- What's New in SQL Server 2005 { http://www.microsoft.com/sql/prodinfo/overview/whats-new-in-sqlserver2005.mspx }