SQL Rank

The SQL Rank function was introduced in SQL Server 2005 and is part of a family of ranking functions. In order to explain the SQL Rank function, we need to look at it in context with the other rank functions.

• RANK
• DENSE_RANK
• ROW_NUMBER
• NTILE

This list may seem overwhelming, however most of the ranking functions are rather similar.

First, the syntax. Each ranking function takes a mandatory parameter of a column or value used in order to “rank”. Let’s look at an example run against the adventureworks light database.

[cc lang=”sql”]
SELECT
UnitPrice = UnitPrice
,RowNumber = ROW_NUMBER() OVER (ORDER BY UnitPrice)
,Rank = RANK() OVER (ORDER BY UnitPrice)
,DenseRank = DENSE_RANK() OVER (ORDER BY UnitPrice)
,NTile = NTile(100) over (ORDER BY UnitPrice)
FROM SalesLT.SalesOrderDetail
[/cc]

Looking at the results of the query provides the best explanation for the functions.

From this list we can compare the UnitPrice in order to determine what each function is doing.

1. The rownumber function, simply add one number per each row that is returned.
2. The Rank function will compare the unit price and provide an integer based on it’s comparison with the previous row. If they are equal it will return the same rank. Internally, the rank will be incremented by one for each row (whether it is equal or not) and the next non-equal value will display the next internal rank. (I know this is a tough sentence, just look at the example).
3. Dense_Rank performs the same as rank, except it does not keep an internal increment and instead simply increments the count by one when the value is not equal
4. Ntile is a beast of it’s own. It simply takes the entire result set, and divides it equally into the number of segments specified in the parameter. In this case 100.