RANKING Functions – ROW_NUMBER, RANK, DENSE_RANK, NTILE in Sql Server

      Rank means: ‘Placing things by merit/grades/preference and assigning a number to it‘

The T-SQL ranking functions ROW_NUMBER(), RANK() and DENSE_RANK() were introduced for ranking of rows in SQL Server 2005.

There syntax is as following:

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.

note : only [<partition_by_clause>] is Optional, and everything else is mandatory.

So what is partition ranking ? 

Suppose we want to rank employees in a company according to their salaries. Then a simple rank function will suffice. But what if i want this ranking to be done not in the whole company but in their department to find whose salary is greater than other employees in a Accounts Department. So here, the ‘Partition By‘ comes into picture, where the result set is divided into parts and then ranking is performed.

Example :

Create a simple table:

1 2 3 4 5 6 7 8 9 10 11

CREATE TABLE Customer_Orders   (   OrderId INT IDENTITY(1,1) NOT NULL,   CustomerId INT,   Total_Amt decimal (10,2)   )

Insert values into this table :

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

INSERT Customer_Orders (CustomerId, Total_Amt) SELECT 1, 100 UNION SELECT 1, 2000 UNION SELECT 1, 560 UNION SELECT 1, 1000 UNION SELECT 2, 1000 UNION SELECT 2, 2500 UNION SELECT 2, 500 UNION SELECT 2, 1500 UNION SELECT 3, 1500

Here i am using the following query to get the data for different ranking functions depending on the value of Total_Amt:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

SELECT *,   ROW_NUMBER() OVER (ORDER BY Total_Amt DESC) AS RN,   ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY Total_Amt DESC) AS RN_P,   RANK() OVER (ORDER BY Total_Amt DESC) AS R,   RANK() OVER (PARTITION BY CustomerId ORDER BY Total_Amt DESC) AS R_P,   DENSE_RANK() OVER (ORDER BY Total_Amt DESC) AS DR,   DENSE_RANK() OVER (PARTITION BY CustomerId ORDER BY Total_Amt DESC) AS DR_P   FROM Customer_Orders   ORDER BY Total_Amt DESC

The result :