TechDays 2008T-SQL Queries: Tips and Techniques Part 2 of 3

More on T-SQL: Tips and Techniques

This time I will talk about Ranking

There are four functions of ranking, but I will speak of only two: rank() and dense_rank()

Let's imagine that in a competition, two first places. There are two ways to classify the competitors. The first one, the first two competitors classify themselves as first and the third classified as third. If there are three first classified, he fourth competitor will sort itself in fourth. This is the rank() function.

The second way, the third competitor ranks up second, after the first position. This is dense_rank ().

The syntax of the ranking functions:

Rank() over ([<partition by>] <order by>)
Dense_Rank() over ([<partition by>] <order by>)

The <order by> is mandatory and means the column, which will classify and in which direction

The <partition by> is optional and is as a group. He left us classify the expression based on partition

Example:

We want to create a list of all customers around the world and rank them by sales:

WITH customerCTE
AS
(
SELECT Sales.Customer.CustomerID, Sum(Sales.SalesOrderHeader.TotalDue) AS TotalDue, Sales.SalesTerritory.Name AS Region
FROM Sales.Customer INNER JOIN
Sales.SalesOrderHeader ON Sales.Customer.CustomerID = Sales.SalesOrderHeader.CustomerID INNER JOIN
Sales.SalesTerritory ON Sales.Customer.TerritoryID = Sales.SalesTerritory.TerritoryID AND Sales.SalesOrderHeader.TerritoryID = Sales.SalesTerritory.TerritoryID
GROUP BY Customer.customerid, SalesTerritory.name
)

SELECT *, Rank() OVER (ORDER BY TotalDue desc) AS Rank
FROM customerCTE


We use a CTE for total sales per customer, and then classify them and we get:

CustomerID        TotalDue              Region     Rank
678                 1179857,4657         Canada         1
697                 1179475,8399         Northwest      2
170                 1134747,4413         Southeast      3
328                 1084439,0265         Southwest      4
514                 1074154,3035         Canada         5
155                 1045197,0498         Canada         6

Now, let's say that what I want is the ranking of sales by region. Simple, use partition:


WITH customerCTE
AS
(
SELECT Sales.Customer.CustomerID, Sum(Sales.SalesOrderHeader.TotalDue) AS TotalDue, Sales.SalesTerritory.Name AS Region
FROM Sales.Customer INNER JOIN
Sales.SalesOrderHeader ON Sales.Customer.CustomerID = Sales.SalesOrderHeader.CustomerID INNER JOIN
Sales.SalesTerritory ON Sales.Customer.TerritoryID = Sales.SalesTerritory.TerritoryID AND Sales.SalesOrderHeader.TerritoryID = Sales.SalesTerritory.TerritoryID
GROUP BY Customer.customerid, SalesTerritory.name
)

SELECT *, Rank() OVER (PARTITION BY region ORDER BY TotalDue desc) AS Rank
FROM customerCTE

Using the partition by, I have my work done:

CustomerID        TotalDue              Region         Rank
678                 1179857,4657         Canada          1
514                 1074154,3035         Canada           2
155                 1045197,0498         Canada           3
697                 1179475,8399         Northwest        1
170                 1134747,4413         Southeast        1
328                 1084439,0265         Southwest        1


And that's ranking.
Hope you've understand
Cheers