SQL RANKING FUNCTIONS

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

 

Advertisements