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:

[table id=25/]

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.

[codesyntax lang=”tsql”]

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

[/codesyntax]

[codesyntax lang=”tsql”]

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

[/codesyntax]

[info]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.[/info]

[codesyntax lang=”tsql”]

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

[/codesyntax]

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.

[codesyntax lang=”tsql”]

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

[/codesyntax]

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

[codesyntax lang=”tsql”]

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

[/codesyntax]

[info]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.[/info]

[codesyntax lang=”tsql”]

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

[/codesyntax]

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.

[codesyntax lang=”tsql”]

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

[/codesyntax]

[codesyntax lang=”tsql”]

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

[/codesyntax]

[info]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.[/info]

[codesyntax lang=”tsql”]

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

[/codesyntax]

Comparing deference between RANK and DENSE_RANK:

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

[codesyntax lang=”tsql”]

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

[/codesyntax]

[codesyntax lang=”tsql”]

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

[/codesyntax]

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.

[codesyntax lang=”tsql”]

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

[/codesyntax]

[codesyntax lang=”tsql”]

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

[/codesyntax]

[info]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.[/info]

[codesyntax lang=”tsql”]

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

[/codesyntax]

All Ranking Functions in one Query:

[codesyntax lang=”tsql”]
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

[/codesyntax]

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.

Share This Story

Share on facebook
Share on twitter
Share on linkedin
Share on whatsapp

Share Your Comments

About The Author

Search Articles

Categories

Follow Fard Solutions

Share Your Comments

Fard Solutions Sdn. Bhd. Company was established in 2012 in Kuala Lumpur. We are engaged in development, implementation, integration and maintenance of the most advanced database solutions and IT research & development (innovation).

(+60) 03 8994 6788

info@fard-solutions.com

Fard Technology Corp.
700-1199 West Hastings Street,
Vancouver, British Colombia,
V6E 3T5 Canada.

Fard Solutions Sdn. Bhd.
1-1C & 1-2C Incubator 1,
Technology Park Malaysia,
Bukit Jalil, 57000
Kuala Lumpur, Malaysia.

  • Copyrights © 2020 Fard Solutions Sdn. Bhd. , All rights reserved.
  • Privacy Policy
  • Terms of Service
  • A Project By Website Artisan