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.
