SQL Server Window Functions: RANK, DENSE_RANK, NTILE, ROW_NUMBER

Recently I have been finding it more and more useful to work with Window functions. These are sometimes referred to Analytic functions or more commonly Ranking functions:

RANK
DENSE_RANK
NTILE
ROW_NUMBER

These functions essentially "score" each row that is returned by the select statement.  So for example if we look at the Sales.SalesTerritory table in AdventureWorks and run the following query:

SELECT

t.Name,

t.CountryRegionCode,

t.[Group],

t.SalesYTD,

RANK() OVER (ORDER BY SalesYTD ) AS Ranked

FROM sales.SalesTerritory t

This results in the final column contain the "rank" of the record (ascending order), ie if it is the second lowest SalesYTD this column will contain a two. 

These functions become much more powerful, however, when using the optional PARTITION BY clause that can be stipulated.  Using this clause we can "subrank" within partitions: 

SELECT t.Name,

t.CountryRegionCode,

t.[Group],

t.SalesYTD,

RANK() OVER (PARTITION BY [Group] ORDER BY SalesYTD) AS SubRank,

RANK() OVER (ORDER BY SalesYTD) AS OverallRank

FROM sales.SalesTerritory t

The results of this query display each record, ranked within its Group (Europe, North America, Pacific):

Name CountryRegionCode Group    SalesYTD      SubRank OverallRank

-------------------------------------------------- ----------------- ---Northeast      US North America 2402176.8476  1       1

Southeast      US North America 2538667.2515  2       2

Central        US North America 3072175.118   3       3

Germany        DE Europe        3805202.3478  1       4

France         FR Europe        4772398.3078  2       5

United Kingdom GB Europe        5012905.3656  3       6

Australia      AU Pacific       5977814.9154  1       7

Canada         CA North America 6771829.1376  4       8

Northwest      US North America 7887186.7882  5       9

Southwest      US North America 10510853.8739 6       10

(10 row(s) affected)

This makes it very easy for us to produce reports that return, say, only the top performers in each region.

These functions avoid the need to write subqueries or CTEs to produce their results and are therefore highly efficient.  More details on these four functions can be found on technet here.

In the next post we'll look at additional lesser known window functions within SQL Server.