Ranking Functions

The estimated reading time for this post is 8 minutes

SQL server provides many built-in functions, ranging from those that perform data type conversion, to those that aggregate and analyze groups of rows. Functions built-into SQL server can be categorized as below:

Functions Category Description
Aggregate FunctionsTake one or more input values, return a single summarizing value
Analytic FunctionsCompute an aggregate value based on a group of rows, can return multiple rows for each group
Ranking FunctionsReturns a ranking value for each row in partition
Row Set FunctionsReturn an object that can be used like table references in an SQL statement
Scalar FunctionsOperate on a single row, return a single value

In this blog I want to write about ranking functions. As it mentioned in above table, ranking functions return a ranking value for each rows in a partition, depending on the function that is used, some rows might receive the same value as other rows. Ranking functions are nondeterministic.

There four type of ranking functions as below:

  • RANK
  • NTILE
  • DENSE_RANK
  • ROW_NUMBER

Each of the window functions will be explained separately with relevant examples.

Rank Function

Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question. It provides the same numeric value for ties ( for example 1, 2, 2, 4, 5 ). Rank is a temporary value calculated when the query is run. OVER clause with ORDER BY is compulsory in RANK function and if you don’t use PARTITION BY in OVER clause, SQL server engine will consider whole result set as one partition.

SELECT PP.FirstName+' '+pp.LastName AS Fullname, SUM(totaldue) AS Totaldue , Territoryid ,
 rank() OVER (ORDER BY SUM(totaldue) DESC) AS Totaldue_Ranking
FROM sales.salesorderheader AS SOH INNER join
person.person AS PP ON SOH.salespersonid=PP.BusinessEntityID
GROUP BY PP.FirstName+' '+pp.LastName , TerritoryID

SELECT PP.FirstName+' '+pp.LastName AS Fullname, SUM(totaldue) AS Totaldue , Territoryid ,
 rank() OVER (partition BY territoryid ORDER BY SUM(totaldue) DESC) AS Totaldue_Ranking
FROM sales.salesorderheader AS SOH INNER join
person.person AS PP ON SOH.salespersonid=PP.BusinessEntityID
GROUP BY PP.FirstName+' '+pp.LastName , TerritoryID

If you don’t use ORDER BY clause at the end of your query, SQL server engine will order result set according to PARTITION BY in RANK at first and then RANKING in each partition at second step automatically. So the above result set is same as below result set.

SELECT PP.FirstName+' '+pp.LastName AS Fullname, SUM(totaldue) AS Totaldue , Territoryid ,
 rank() OVER (partition BY territoryid ORDER BY SUM(totaldue) DESC) AS Totaldue_Ranking
FROM sales.salesorderheader AS SOH INNER join
person.person AS PP ON SOH.salespersonid=PP.BusinessEntityID
GROUP BY PP.FirstName+' '+pp.LastName , TerritoryID
ORDER BY Territoryid , Totaldue_Ranking

NTile Function

Distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs. OVER clause with ORDER BY is compulsory in NTILE function and if you don’t use PARTITION BY in OVER clause, SQL server engine will consider whole result set as one partition.

SELECT PP.FirstName+' '+pp.LastName AS Fullname, SUM(totaldue) AS Totaldue ,Territoryid , 
 NTILE(4) OVER ( ORDER BY SUM(totaldue) DESC ) AS Totaldue_NTILE_Ranking
FROM sales.salesorderheader AS SOH INNER join
person.person AS PP ON SOH.salespersonid=PP.BusinessEntityID
GROUP BY PP.FirstName+' '+pp.LastName , Territoryid

In above example SQL server engine considered all 35 rows in one partition and divided Totaldue_NTILE_Ranking column into 4 tiles.

SELECT PP.FirstName+' '+pp.LastName AS Fullname, SUM(totaldue) AS Totaldue ,Territoryid , 
 NTILE(4)OVER( partition BY Territoryid ORDER BY SUM(totaldue)DESC) AS Totaldue_NTILE_Ranking
FROM sales.salesorderheader AS SOH INNER join
person.person AS PP ON SOH.salespersonid=PP.BusinessEntityID
GROUP BY PP.FirstName+' '+pp.LastName , Territoryid

If you don’t use ORDER BY clause at the end of your query, SQL server engine will order result set according to PARTITION BY in NTILE at first and then RANKING in each partition at second step automatically. So the above result set is same as below result set.

SELECT PP.FirstName+' '+pp.LastName AS Fullname, SUM(totaldue) AS Totaldue ,Territoryid , 
 NTILE(4)OVER( partition BY Territoryid ORDER BY SUM(totaldue)DESC) AS Totaldue_NTILE_Ranking
FROM sales.salesorderheader AS SOH INNER join
person.person AS PP ON SOH.salespersonid=PP.BusinessEntityID
GROUP BY PP.FirstName+' '+pp.LastName , Territoryid
ORDER BY Territoryid , Totaldue_NTILE_Ranking

Dense_Rank Function

Returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question. OVER clause with ORDER BY is compulsory in DENSE_RANK function and if you don’t use PARTITION BY in OVER clause, SQL server engine will consider whole result set as one partition.

SELECT PP.FirstName+' '+pp.LastName AS Fullname, SUM(totaldue) AS Totaldue ,Territoryid , 
 Dense_Rank() OVER (ORDER BY SUM(totaldue) DESC ) AS Totaldue_Dense_Ranking
FROM sales.salesorderheader AS SOH INNER join
person.person AS PP ON SOH.salespersonid=PP.BusinessEntityID
GROUP BY PP.FirstName+' '+pp.LastName , Territoryid

SELECT PP.FirstName+' '+pp.LastName AS Fullname, SUM(totaldue) AS Totaldue ,Territoryid , 
Dense_Rank()OVER(partition BY Territoryid ORDER BY SUM(totaldue)DESC)AS Totaldue_Dense_Ranking
FROM sales.salesorderheader AS SOH INNER join
person.person AS PP ON SOH.salespersonid=PP.BusinessEntityID
GROUP BY PP.FirstName+' '+pp.LastName , Territoryid

If you don’t use ORDER BY clause at the end of your query, SQL server engine will order result set according to PARTITION BY in NTILE at first and then RANKING in each partition at second step automatically. So the above result set is same as below result set.

SELECT PP.FirstName+' '+pp.LastName AS Fullname, SUM(totaldue) AS Totaldue ,Territoryid , 
Dense_Rank()OVER(partition BY Territoryid ORDER BY SUM(totaldue)DESC)AS Totaldue_Dense_Ranking
FROM sales.salesorderheader AS SOH INNER join
person.person AS PP ON SOH.salespersonid=PP.BusinessEntityID
GROUP BY PP.FirstName+' '+pp.LastName , Territoryid
ORDER BY Territoryid , Totaldue_Dense_Ranking

Comparing deference between RANK and DENSE_RANK:

You can see deference between these 2 functions in example as below:

SELECT PP.FirstName+' '+pp.LastName AS Fullname, COUNT(salesordernumber) AS Totalsale ,Territoryid , 
Rank() OVER (ORDER BY COUNT(salesordernumber) DESC ) AS Totalsale_Ranking
FROM sales.salesorderheader AS SOH INNER join
person.person AS PP ON SOH.salespersonid=PP.BusinessEntityID
GROUP BY PP.FirstName+' '+pp.LastName , Territoryid

SELECT PP.FirstName+' '+pp.LastName AS Fullname, COUNT(salesordernumber) AS Totalsale ,Territoryid , 
Dense_Rank() OVER (ORDER BY COUNT(salesordernumber) DESC ) AS Totalsale_Dense_Ranking
FROM sales.salesorderheader AS SOH INNER join
person.person AS PP ON SOH.salespersonid=PP.BusinessEntityID
GROUP BY PP.FirstName+' '+pp.LastName , Territoryid

Row_Number Function

This function numbers the output of a result set. More specifically, returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition. ROW_NUMBER and RANK are similar but ROW_NUMBER numbers all rows sequentially (1, 2, 3, 4, 5).Same as others OVER clause with ORDER BY is compulsory in ROW_NUMBER function and if you don’t use PARTITION BY in OVER clause, SQL server engine will consider whole result set as one partition.

SELECT PP.FirstName+' '+pp.LastName AS Fullname, SUM(totaldue) AS Totaldue ,Territoryid , 
Row_number() OVER (ORDER BY SUM(totaldue) DESC ) AS Totaldue_Row_Numbering
FROM sales.salesorderheader AS SOH INNER join
person.person AS PP ON SOH.salespersonid=PP.BusinessEntityID
GROUP BY PP.FirstName+' '+pp.LastName , Territoryid

SELECT PP.FirstName+' '+pp.LastName AS Fullname, SUM(totaldue) AS Totaldue ,Territoryid , 
Row_number() OVER (partition BY Territoryid ORDER BY SUM(totaldue) DESC ) AS Totaldue_Row_Numbering
FROM sales.salesorderheader AS SOH INNER join
person.person AS PP ON SOH.salespersonid=PP.BusinessEntityID
GROUP BY PP.FirstName+' '+pp.LastName , Territoryid

Same as others if you don’t use ORDER BY clause at the end of your query, SQL server engine will order result set according to PARTITION BY in ROW_NUMBER at first and then RANKING in each partition sequentially at second step automatically. So the above result set is same as below result set.

SELECT PP.FirstName+' '+pp.LastName AS Fullname, SUM(totaldue) AS Totaldue ,Territoryid , 
Row_number() OVER (partition BY Territoryid ORDER BY SUM(totaldue) DESC ) AS Totaldue_Row_Numbering
FROM sales.salesorderheader AS SOH INNER join
person.person AS PP ON SOH.salespersonid=PP.BusinessEntityID
GROUP BY PP.FirstName+' '+pp.LastName , Territoryid
ORDER BY Territoryid , Totaldue_Row_Numbering

All Ranking Functions in one Query:

SELECT PP.FirstName+' '+pp.LastName AS Fullname, SUM(totaldue) AS Totaldue ,Territoryid ,
 Rank() OVER( partition BY Territoryid ORDER BY SUM(totaldue) DESC) AS Totaldue_Ranking ,
 NTile(4) OVER (partition BY Territoryid ORDER BY SUM(totaldue) DESC) AS Totaldue_NTILE_Ranking ,
 Dense_Rank() OVER ( partition BY Territoryid ORDER BY SUM(totaldue) DESC ) AS Totaldue_Dense_Ranking ,
 Row_number() OVER (partition BY Territoryid ORDER BY SUM(totaldue) DESC ) AS Totaldue_Row_Numbering
 FROM sales.salesorderheader AS SOH INNER join
 person.person AS PP ON SOH.salespersonid=PP.BusinessEntityID
 GROUP BY PP.FirstName+' '+pp.LastName , Territoryid

I hope this blog post was also useful and informative for you. Please share with your social media connections if you find it well and informative. Stay tuned for more DML and DDL blog posts.

Saeed Jabarpourfard

Saeed is a Microsoft Certified Professional and Microsoft Certified Solutions Associate: SQL Server 2016 Database Developer, with extensive experience in Database Development and Administration. He has been working with SQL Server since year 2010, and he used SQL Server 2008 R2 as his first RDBMS.

More Posts - Website