Lets see some of the Ranking functions in SQL.
RANK()
Returns the rank of each row within the result set
USE AdventureWorks2008R2;
GO
SELECT i.ProductID, p.Name, i.LocationID, i.Quantity
,RANK() OVER
(ORDER BY i.Quantity DESC) AS Rank
FROM Production.ProductInventory AS i
INNER JOIN Production.Product AS p
ON i.ProductID = p.ProductID
WHERE i.LocationID BETWEEN 3 AND 4
ORDER BY i.LocationID;
The Result Set for the above Query will be,
| ProductID | Name | LocationID | Quantity | Rank |
| 495 | Paint – Blue | 3 | 49 | 1 |
| 494 | Paint – Silver | 3 | 49 | 1 |
| 493 | Paint – Red | 3 | 41 | 3 |
| 496 | Paint – Yellow | 3 | 30 | 5 |
| 492 | Paint – Black | 3 | 17 | 8 |
| 492 | Paint – Black | 4 | 14 | 9 |
| 494 | Paint – Silver | 4 | 12 | 10 |
| 496 | Paint – Yellow | 4 | 25 | 6 |
| 493 | Paint – Red | 4 | 24 | 7 |
| 495 | Paint – Blue | 4 | 35 | 4 |
If two or more rows tie for a rank, each tied rows receives the same rank.
As you see the first two rows have the same quantity. So the Rank is also Same.
But the third row got the rank as 3. It means, the Rank function will skip the Rank 2,
if two rows got the same rank.
Therefore, the RANK function does not always return consecutive integers
For all the Ranking functions, you can always use Partition By to segregate the result set.
partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied.
For Example, in the above query, if we apply Partition by
SELECT i.ProductID, p.Name, i.LocationID, i.Quantity
,RANK() OVER
(PARTITION BY i.LocationID ORDER BY i.Quantity DESC) AS Rank
FROM Production.ProductInventory AS i
INNER JOIN Production.Product AS p
ON i.ProductID = p.ProductID
WHERE i.LocationID BETWEEN 3 AND 4
ORDER BY i.LocationID;
The result set will be
| ProductID | Name | LocationID | Quantity | Rank |
| 494 | Paint – Silver | 3 | 49 | 1 |
| 495 | Paint – Blue | 3 | 49 | 1 |
| 493 | Paint – Red | 3 | 41 | 3 |
| 496 | Paint – Yellow | 3 | 30 | 4 |
| 492 | Paint – Black | 3 | 17 | 5 |
| 495 | Paint – Blue | 4 | 35 | 1 |
| 496 | Paint – Yellow | 4 | 25 | 2 |
| 493 | Paint – Red | 4 | 24 | 3 |
| 492 | Paint – Black | 4 | 14 | 4 |
| 494 | Paint – Silver | 4 | 12 | 5 |
As you see clearly from the result set that the result set has been partitioned based on the location.
Also the Ranking has been applied twice, as there are two location ID here.
DENSERANK()
Dense Rank is same as the Rank function, except that it will return consecutive integers for ranking.
SELECT i.ProductID, p.Name, i.LocationID, i.Quantity
,DENSE_RANK() OVER
(ORDER BY i.Quantity DESC) AS Rank
FROM Production.ProductInventory AS i
INNER JOIN Production.Product AS p
ON i.ProductID = p.ProductID
WHERE i.LocationID BETWEEN 3 AND 4
The Result set will be,
| ProductID | Name | LocationID | Quantity | Rank |
| 495 | Paint – Blue | 3 | 49 | 1 |
| 494 | Paint – Silver | 3 | 49 | 1 |
| 493 | Paint – Red | 3 | 41 | 2 |
| 495 | Paint – Blue | 4 | 35 | 3 |
| 496 | Paint – Yellow | 3 | 30 | 4 |
| 496 | Paint – Yellow | 4 | 25 | 5 |
| 493 | Paint – Red | 4 | 24 | 6 |
| 492 | Paint – Black | 3 | 17 | 7 |
| 492 | Paint – Black | 4 | 14 | 8 |
| 494 | Paint – Silver | 4 | 12 | 9 |
If you apply Partition By on the Location ID,
SELECT i.ProductID, p.Name, i.LocationID, i.Quantity
,DENSE_RANK() OVER
(Partition by i.locationId ORDER BY i.Quantity DESC) AS Rank
FROM Production.ProductInventory AS i
INNER JOIN Production.Product AS p
ON i.ProductID = p.ProductID
WHERE i.LocationID BETWEEN 3 AND 4
the result set will be
| ProductID | Name | LocationID | Quantity | Rank |
| 494 | Paint – Silver | 3 | 49 | 1 |
| 495 | Paint – Blue | 3 | 49 | 1 |
| 493 | Paint – Red | 3 | 41 | 2 |
| 496 | Paint – Yellow | 3 | 30 | 3 |
| 492 | Paint – Black | 3 | 17 | 4 |
| 495 | Paint – Blue | 4 | 35 | 1 |
| 496 | Paint – Yellow | 4 | 25 | 2 |
| 493 | Paint – Red | 4 | 24 | 3 |
| 492 | Paint – Black | 4 | 14 | 4 |
| 494 | Paint – Silver | 4 | 12 | 5 |
As you see clearly from the result set that the result set has been partitioned based on the location.
Also the Ranking has been applied twice, as there are two location ID here.
ROW_NUMBER()
Row Number is same as the DenSe_Rank except For rows that have duplicate values,numbers are arbitarily assigned.
For the below query,
SELECT i.ProductID, p.Name, i.LocationID, i.Quantity
,ROW_NUMBER() OVER
(ORDER BY i.Quantity DESC) AS RowNumber
FROM Production.ProductInventory AS i
INNER JOIN Production.Product AS p
ON i.ProductID = p.ProductID
WHERE i.LocationID BETWEEN 3 AND 4
The Result Set will be
| ProductID | Name | LocationID | Quantity | RowNumber |
| 495 | Paint – Blue | 3 | 49 | 1 |
| 494 | Paint – Silver | 3 | 49 | 2 |
| 493 | Paint – Red | 3 | 41 | 3 |
| 495 | Paint – Blue | 4 | 35 | 4 |
| 496 | Paint – Yellow | 3 | 30 | 5 |
| 496 | Paint – Yellow | 4 | 25 | 6 |
| 493 | Paint – Red | 4 | 24 | 7 |
| 492 | Paint – Black | 3 | 17 | 8 |
| 492 | Paint – Black | 4 | 14 | 9 |
| 494 | Paint – Silver | 4 | 12 | 10 |
As you see the First two rows have the same quantity. So both should be ranked same.
But Row Number function arbitarily assigned a Rank for the second row.
If we do PARTITION By on the above Query
SELECT i.ProductID, p.Name, i.LocationID, i.Quantity
,ROW_NUMBER() OVER
(Partition by i.locationId ORDER BY i.Quantity DESC) AS RowNumber
FROM Production.ProductInventory AS i
INNER JOIN Production.Product AS p
ON i.ProductID = p.ProductID
WHERE i.LocationID BETWEEN 3 AND 4
The result set will be
| ProductID | Name | LocationID | Quantity | RowNumber |
| 494 | Paint – Silver | 3 | 49 | 1 |
| 495 | Paint – Blue | 3 | 49 | 2 |
| 493 | Paint – Red | 3 | 41 | 3 |
| 496 | Paint – Yellow | 3 | 30 | 4 |
| 492 | Paint – Black | 3 | 17 | 5 |
| 495 | Paint – Blue | 4 | 35 | 1 |
| 496 | Paint – Yellow | 4 | 25 | 2 |
| 493 | Paint – Red | 4 | 24 | 3 |
| 492 | Paint – Black | 4 | 14 | 4 |
| 494 | Paint – Silver | 4 | 12 | 5 |
NTILE()
Displays the rows in an ordered partition into a specified number of groups.
The Groups are Consecutive Integers starting at 1.
SELECT i.ProductID, p.Name, i.LocationID, i.Quantity
,NTILE(2) OVER
(ORDER BY i.Quantity DESC) AS RowNumber
FROM Production.ProductInventory AS i
INNER JOIN Production.Product AS p
ON i.ProductID = p.ProductID
WHERE i.LocationID BETWEEN 3 AND 4
The above query will give the following result set.
| ProductID | Name | LocationID | Quantity | RowNumber |
| 495 | Paint – Blue | 3 | 49 | 1 |
| 494 | Paint – Silver | 3 | 49 | 1 |
| 493 | Paint – Red | 3 | 41 | 1 |
| 495 | Paint – Blue | 4 | 35 | 1 |
| 496 | Paint – Yellow | 3 | 30 | 1 |
| 496 | Paint – Yellow | 4 | 25 | 2 |
| 493 | Paint – Red | 4 | 24 | 2 |
| 492 | Paint – Black | 3 | 17 | 2 |
| 492 | Paint – Black | 4 | 14 | 2 |
| 494 | Paint – Silver | 4 | 12 | 2 |
As we used NTILE(2), it divides the result set into 2 groups.
And it assigned rank for the 1st group and rank 2 for the second group.
If we used NTILE(3) in the above query, it will divide the result set into three groups.
SELECT i.ProductID, p.Name, i.LocationID, i.Quantity
,NTILE(3) OVER
(ORDER BY i.Quantity DESC) AS RowNumber
FROM Production.ProductInventory AS i
INNER JOIN Production.Product AS p
ON i.ProductID = p.ProductID
WHERE i.LocationID BETWEEN 3 AND 4
The Result set will be
| ProductID | Name | LocationID | Quantity | RowNumber |
| 495 | Paint – Blue | 3 | 49 | 1 |
| 494 | Paint – Silver | 3 | 49 | 1 |
| 493 | Paint – Red | 3 | 41 | 1 |
| 495 | Paint – Blue | 4 | 35 | 1 |
| 496 | Paint – Yellow | 3 | 30 | 2 |
| 496 | Paint – Yellow | 4 | 25 | 2 |
| 493 | Paint – Red | 4 | 24 | 2 |
| 492 | Paint – Black | 3 | 17 | 3 |
| 492 | Paint – Black | 4 | 14 | 3 |
| 494 | Paint – Silver | 4 | 12 | 3 |
Since the total no. of rows is 10, and we are not able to divide it equally by 3,
The NTILE function assigned 1 for first four rows and 2 for next 3 rows and 3 for final 3 rows.
If you apply Partition in the NTILE function,
SELECT i.ProductID, p.Name, i.LocationID, i.Quantity
,NTILE(2) OVER
(Partition by i.locationId ORDER BY i.Quantity DESC) AS RowNumber
FROM Production.ProductInventory AS i
INNER JOIN Production.Product AS p
ON i.ProductID = p.ProductID
WHERE i.LocationID BETWEEN 3 AND 4
The result set will be
| ProductID | Name | LocationID | Quantity | RowNumber |
| 494 | Paint – Silver | 3 | 49 | 1 |
| 495 | Paint – Blue | 3 | 49 | 1 |
| 493 | Paint – Red | 3 | 41 | 1 |
| 496 | Paint – Yellow | 3 | 30 | 2 |
| 492 | Paint – Black | 3 | 17 | 2 |
| 495 | Paint – Blue | 4 | 35 | 1 |
| 496 | Paint – Yellow | 4 | 25 | 1 |
| 493 | Paint – Red | 4 | 24 | 1 |
| 492 | Paint – Black | 4 | 14 | 2 |
| 494 | Paint – Silver | 4 | 12 | 2 |
Keep Software Weird 